As we recently tried to migrate a Coldfusion 7 Project to Coldfusion 9.0.1 we noticed that obviously handling of variables in Microsoft SQL Server JDBC interface in combination with MSSQL Server 2008 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.
UPDATE 06.02.2013
There are three ways to get this to work in CF9 and also CF10. Use dateFormat() and timeFormat() and send the datetime object as ISO 8601 string -> ‚yyyy-mm-ddTHH:mm:ss‘ or try using cfqueryparam, and the last would be using SQL functions like getDate() instead of #now()#.
INSERT INTO [mytable] ([datefield]) values ('#dateFormat(now(),'yyyy-mm-dd')#T#timeFormat(now(),'HH:mm:ss')#')
INSERT INTO [mytable] ([datefield]) values (<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#now()#">)
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()“.
Gl and hf!