Dates in mySQL need to be quoted. So your SQL would look like this:
SELECT columns FROM table WHERE date1 BETWEEN '#startdate#' AND '#enddate#' <!----------------//------ andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --------------//---------> -----Original Message----- From: Christopher Lavie [mailto:[EMAIL PROTECTED] Sent: Saturday, October 07, 2006 6:55 PM To: CF-Talk Subject: ColdFusion SQL question This probably has a simple, obvious solution, but it's frustrating the hell out of me. I am using ColdFusion MX 7 and a MySQL database. I have a database of surveys taken with each record having a date that the survey was taken. I am trying to pull records between two dates. This code worked just fine with an Access database but not with the MySQL database: <cfset Session.WhereClause = Session.WhereClause & " AND ((Survey.ShopDate BETWEEN ## #NewBeginDate# ## AND ## #NewEndDate# ##))"> (Later, I apply the Session.WhereClause variable to the SQL statement.) The following code works just fine when I use it in the database itself (obviously replacing the variables with actual dates). Unfortunately it returns no records in my ColdFusion code when I leave off the single quotes, and it returns a syntax error when I include the quotes. <cfset Session.WhereClause = Session.WhereClause & " AND Survey.ShopDate >= '#NewBeginDate#' AND Survey.ShopDate <= '#NewEndDate#' "> I have tried the CreateODBCDateTime function to no avail. Can anybody help me? I'm at my wits end. Thanks! Chris ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255955 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
