[cfaussie] Re: Date problems in Query of Queries

[Phil Evans]

Phil,   I haven't tried queries of queries, but from an SQL point of view I would try the following:   1. <cfset dateTo =  CreateODBCDateTime(DateAdd('D', 7, dateFrom))>   2. WHERE      Transaction_DATE >= #DateFrom# AND Transaction_DATE <= #DateTo# HTH, Phil E. ----- Original Message ----- From: Phil Rasmussen Newsgroups: cfaussie To: CFAussie Mailing List Sent: Tuesday, March 04, 2003 4:01 PM Subject: [cfaussie] Date problems in Query of Queries Hey all, just wondering if anyone has experienced problems using DateStamps in Query of Queries......in WHERE clauses specifically.  All I am trying to do is a simple WHERE Date BETWEEN #DateFrom# AND #DateTo#   but I keep getting syntax errors. Both date variables are created using CreateODBCDateTime() and the query works perfectly ok directly off the database....but as soon as I run it as a query of queries, I get the syntax error. It makes no sense. I did read a few posts about possible bugs in CFMX Query of Queries implementation. Query and Error is below:   <cfset dateFrom = CreateODBCDateTime(CreateDate(2003, 02, 01))> <cfset dateTo =  DateAdd('D', 7, dateFrom)>   <cfquery name="NewBranchDetails" dbtype="query">    SELECT       Transaction_Date, Sum(Value_Price) as TotalSales    FROM          tempQuery    WHERE      Transaction_DATE >= #DateFrom# AND <= #DateTo#    GROUP BY   Transaction_Date    ORDER BY   Transaction_Date asc </cfquery>   The error message I get is:     SELECT Transaction_Date, Sum(Value_Price) as TotalSales FROM tempQuery WHERE Transaction_DATE >= &apos;{ts &apos;2003-02-01 00:00:00&apos;}&apos; GROUP BY   Query Of Queries syntax error. Encountered "2003" at line 4, column 48.   The error occurred in C:\Inetpub\wwwroot\Graphing\index.cfm: line 116 114 : GROUP BY Transaction_Date 115 : ORDER BY Transaction_Date asc 116 : </cfquery> 117 : 118 : Thanks,   Phil  --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/