SQLException with #now()#

Question

We are attempting to build a backup server for a legacy CF10 application - "just in case", whilst rebuilding the application - properly!

"Everything" is the same as far as we can tell:

• MS SQL 2014 12.0.6179.1 (compatibility SQL Server 2008)
• ColdFusion 10,284825
• ODBC "SQL Server" drive: 10.00.14393.7155 (not sure this matters)
• Windows Server 2016 (CF admin settings summary has this in both cases down as Windows Server 2012)
• ODBC drivers "ODBC Driver 11 for SQL Server", "SQL Server" and "SQL Server Native Client 11.0" are the same on both

We have one problem - now() is not working correctly. This works on the production server but fails on the new:

INSERT INTO User_Log VALUES ('test', 'None', 'Log In', 'The user logged in', #now()#)

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"

This is NOT a good way of doing things, but this is a legacy application and now() is used A LOT! So hopefully there's a platform setting.......

This SQL statement executes within Management Studio AND within cfquery:

INSERT INTO User_Log VALUES ('test', 'None', 'Log In', 'The user logged in', {ts '2024-07-23 16:44:11'})

They should be identical - i.e. this INSERT statement is exactly what was shown in the SQLDataException.

Any suggestions?