Troubleshooting & How-Tos 📡 🔍 Programming

Dates and ColdFusion Query of Queries


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 dbtype="query" name="followup">
	select mystuff from mytable
	where mydate = <cfsqlparam value="#somedate#" cfsqltype="cf_sql_date" />

Obviously this example is oversimplified, but you get the idea.