ColdFusion QoQ Date Comparison

ColdFusion Query of Queries are a useful way of filtering an existing, in-memory recordset by executing a SQL query against the dataset.

Unfortunately, the documentation and more importantly, error handling for this otherwise cool ColdFusion feature are a bit lacking. Today I was trying to filter a query by date. The query had columns such as:

articleid,headline,author,publishdate

I needed to get the articles after a certain date and tried to do a simple QoQ:

<cfquery dbtype="query" name="filter">
select * from results WHERE publishdate > #startDate#
</cfquery>

This query returns an error about unsupport type comparision. After trying numerous functions and syntax changes, Ryan suggested I try CFQUERYPARAM, which (unexpectedly) worked perfectly:

<cfquery dbtype="query" name="filter">
select * from results
WHERE publishdate > <cfqueryparam value="#startDate#" cfsqltype="CF_SQL_DATE">
</cfquery>
This got me the exact results I needed. I'm still not exactly sure why the CFQUERYPARAM fixed the problem; my impression was that it was used for doing parameterized queries (prepared statements) against a SQL database, but in this case it appears to be setting some sort of data type flag internally that allows the comparison to work.

If you've been working with ColdFusion for a long time, you may be wondering why I just don't filter the original query (results) rather than use the slower QoQ functionality. In this case though, the query is not coming from a database and doesn't support date filtering - so QoQ is exactly the tool needed to do the filtering.