Use cf_sql_timestamp for MS SQL and MySQL date types

Hopefully, if you’re using ColdFusion, you’re already using cfqueryparam in your queries for better performance and security against SQL Injection attacks. If you’re not, you should read this.

When using cfqueryparam you specify a value and an SQL type. Dates can be assigned two different types, cf_sql_date and cf_sql_timestamp. cf_sql_date is intended to be used for date types in DB2 and Informix. If you’re using MS SQL Server, you should use cf_sql_timestamp.

cf_sql_timestamp maps to the datetime and smalldatetime data types in MS SQL Server. Although cf_sql_date works in many instances, it does not work in all instances. I ran into this issue when evaluating whether a date equaled a certain date value. For example:


SELECT *
FROM myTable
WHERE create_dt = 

When I ran the query in SQL Query Analyzer (minus the cfqueryparam of course), I received the expected results. When I ran it inside a ColdFusion page I received an empty result set. Once I changed to timestamp, I received my results.


SELECT *
FROM myTable
WHERE create_dt = 

I haven’t tested the code with mySQL, and mySQL isn’t listed in the livedocs cross-reference table so I don’t know what kind of effect can be seen there, though I’d be interested to find out.

UPDATE: I got two great tips from comments. According to sebastiaan cf_sql_timestamp should be used with MySQL as well as MS SQL Server. And Ben Nadel adds that cf_sql_longvarchar should be used for text fields. Great info. Thank you both!

5 thoughts on “Use cf_sql_timestamp for MS SQL and MySQL date types

  1. I used to use cf_sql_date all the time until a few years ago when I was playing around MS SQL Desktop Edition (or something like that) and it literally did not support _date. I finally looked at the documentation and found out that MS SQL was not supposed to support _date in the first place. After that, I started using _timestamp for all date/time fields.

    Another tip – when dealing with Text fields, use _longvarchar as it’s more cross-DB compliant.

  2. I also just found out that if you use cf_sql_date, hours and minutes get erased and zeros are inserted instead. However using cf_sql_timestamp saves hours and minutes information. MS SQL database.

Comments are closed.