As we recently tried to migrate a Coldfusion 7 Project to Coldfusion 9.0.1 we noticed that obviously variables handling in Microsoft SQL Server JDBC interface has changed.
We performed these few tests for example – each of it separatly – and always got the same result:
INSERT INTO [mytable] ([datefield]) values (#createODBCDateTime(now())#) INSERT INTO [mytable] ([datefield]) values (#now()#) UPDATE [mytable] SET [datefield] = #createODBCDateTime(now())# UPDATE [mytable] SET [datefield] = #now()#
The error Coldfusion gave us was a SQLServer JDBC error:
[Macromedia][SQLServer JDBC Driver][SQLServer]Bei der Konvertierung eines varchar-Datentyps in einen smalldatetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.
which means somehting like “The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value” and is obviously caused by the variable being recognized as VARCHAR and not as DATETIME object or at least not converting it the right way.
Whereas in Coldfusion 7 and also 8 it works perfectly.
There are three ways to get this to work in CF9 now. The first is using cfqueryparam, the other is using dateFormat() / timeFormat() and sending the datetime object as string and the last would be using SQL functions like getDate() instead of #now()# which does not work so easily in conjunction with a coldfusion datetime variable of course.
INSERT INTO [mytable] ([datefield]) values (<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#now()#">)
INSERT INTO [mytable] ([datefield]) values ('#dateFormat(now(),'yyyy-dd-mm')# #timeFormat(now(),'HH:mm:ss')#')
INSERT INTO [mytable] ([datefield]) values (getDate())
You can save either datetime or smalldatetime this way.
Btw. the problem happens with the Coldfusion “createODBCTime()” function too, but not with “createODBCDate()”. Ok thx for reading, gl and hf!
