Russell's Blog

The nicest guy you'll never know
Vimeo Flickr Twitter RSS

Coldfusion 9 date and time data type error bug

ColdFusion-9

Moving from Coldfusion 8 to Coldfusion 9, a bug reared its ugly head immediately. If your code either updates or inserts into a SQL datetime column, Coldfusion will spit out a datetime error:

[Macromedia][SQLServer JDBC Driver][SQLServer]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

This happens no matter if you use a variable (eg. #query.date#), #createODBCDateTime()#, or #now()#. This worked without error with Coldfusion before CF 9.

I had to go through all the code, changing all datetime variables to use cfqueryparam cfsqltype=”CF_SQL_TIMESTAMP”, and replacing all occurrence of #now()# to the SQL function to getDate(). Then the code would run without error.

As far as I know this has not been fixed in the Coldfusion 9 hot fixes and updates.

posted under Coldfusion | 1 Comment »