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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.