Dates and ColdFusion Query of Queries

Argh.

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.