[cfaussie] Re: Date problems in Query of Queries

[Phil Rasmussen]

Steve after getting the sucker working it seems that there are column types in QoQ, and they can be forced even further into submission by using <cfqueryparam> within the SQL.....also both Sum() and GroupBy definetly work in QoQ now that my query actually runs error free.   Thanks for the help,   Phil   P.S I think I just flawed your signature phrase ;) hehe "Steve Onnis" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Phil   From what I recall, there is no such thing as column types in QofQ.  Once you start to query a query, all columns are strings and integers.   Also, I dont think you can "group by" with query of query either, and am almost certain you cant use aggregated functions like SUM, COUNT and so on.   Regards Steve Onnis Domain Concept Designs +61 422 337 685 +61 3 9431 4249 http://www.domainconceptdesigns.com [EMAIL PROTECTED] http://www.cfcentral.com.au [EMAIL PROTECTED] ("If you think it can't be done, you haven't asked me!") - Steve Onnis   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Phil Rasmussen Sent: Tuesday, March 04, 2003 4:01 PM To: CFAussie Mailing List 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/