You can have different data types in query of queries, but since ColdFusion does automatic type conversion it will convert a timestamp to a string. You can avoid that if you are creating the columns by converting the timestamp to it's numeric equivalent thusly:
<cfset numberdate = somedate + 0> Once you have done that you should be able to perform BETWEEN comparisons providing the dates in the columns are stored as numeric values (or as dates if the query is from a database). You can do group by and aggregate functions as of CFMX. Lots of information about how to do that is at the following link: http://makeashorterlink.com/?I17921CA3' Spike Stephen Milligan Consultant for hire http://spikefu.blogspot.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Onnis Sent: 04 March 2003 16:10 To: CFAussie Mailing List Subject: [cfaussie] RE: Date problems in Query of Queries 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]://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] 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 >= '{ts '2003-02-01 00:00:00'}' 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/ --- 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/
