If you need to run any date comparisons on a ColdFusion Query of Queries, you need the following:
- In the original database query, select the values as datetime. DO NOT cast them as date. (This is with MSSQL, anyway).
- In the follow-up query, use CFQUERYPARAM and the cf_sql_date type.
I spent waaay too long trying to select subsets of the original query based on dates, and whether I selected them as strings (for implicit conversion) or using query parameters, I just couldn’t get any results out of them. It turned out that since the original query had cast the datetime value as a date, I couldn’t make any comparisons in the query of queries.
Once I removed that cast (originally there to simply group timestamps by day, but I’d since changed the query to group by month), it worked.
<cfquery name="realquery"> select mydate, mystuff from mytable </cfquery> <cfquery dbtype="query" name="followup"> select mystuff from mytable where mydate = <cfsqlparam value="#somedate#" cfsqltype="cf_sql_date" /> </cfquery>
Obviously this example is oversimplified, but you get the idea.