I was recently looking at a report generator, and came across a conflict in the mySql query dealing with the date objects.

The report returns a set of records between two dates, using cfqueryparam, but there seemed to be a problem in running the query using certain dates.

Running it from a browser provided differing results from running it directly in a sql interface. So where was the error?

The original code was using the "cf_sql_date" type to pass the date time off to the sql engine. It appears that this data type is not transferred as a date time value, but as an incompatible numeric value, that is not comparable to the mySql datetime I am querying against.

There are a wealth of articles online about passing date values into a variety of database technologies, but this Adobe post was very helpful.

This article shows a handy table of cfsqltypes and how they map to different database management systems.

Using something like sql profiler you can clearly see the difference between cf_sql_date and cf_sql_timestamp, where cf_sql_date is being truncated to just a date value (actually evaluates as a numeric value) and a full date time stamp that cf_sql_timestamp generates.

Changing the code to the above example completely resolves this issue.