tracker issue : CF-3497870

select a category, or use search below
(searches all categories and all time range)

cfqueryparam cf_sql_timestamp represenation in MS SQL Server results in rounding errors

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Colby Ackerfield / Colby Ackerfield (RgCFDev)

Created: 02/08/2013

Components: Documentation

Versions: 10.0

Failure Type: Data Corruption

Found In Build/Fixed In Build: Final /

Priority/Frequency: Critical / Most users will encounter

Locale/System: English / Platforms All

Vote Count: 0

Problem Description:
The change in representation of a date when using cfqueryparam and the cf_sql_timestamp type with MS SQL SERVER database results in rounding errors. 

With CF9, a date passed to SQL SERVER looks like the following in a capture from SQL Server Profiler

            CF9: exec sp_execute 1,'2013-02-08 08:01:25.847'

In ColdFusion 10 it now appears as:

            CF10: exec sp_execute 1,'2013-02-08 08:01:25.8470000 +00:00'

When the format used in CF10 is inserted, the value stored in SQL Server often differs in the 1000th of a millisecond.

This becomes a problem if that value is used to insert and then subsequently used to retrieve. The exact value used to retrieve doesn't match the rounded value stored. This problem occurs about 70% of the time with the new date string used in CF10. It does not occur in CF9 using the shorter date format.

Steps to Reproduce:

Create cf date = now();
INSERT with date using cfqueryparam
attempt to SELECT on that date with cfqueryparam

Code attached demonstrates the problem.

Actual Result:
 ~70% failure with CF10. 

Expected Result:
 0% failure with CF9.

Any Workarounds:

Use the cfsqltype="cf_sql_varchar" with a manually formatted string to the appropriate precision.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3497870

Deployment Phase:	Release Candidate

External Customer Info:
External Company:  
External Customer Name: RgCFDev
External Customer Email:  
External Test Config: My Hardware and Environment details:

CF10, MS SQL Server 2008 (10.0.5500)


  1. February 09, 2013 00:00:00: 1_cf10_dates_loop.cfm


Adding the following to the connection parameters of the datasource fixes the issue: DateTimeInputParameterType=dateTime From
Comment by External U.
16376 | February 08, 2013 12:24:52 PM GMT
This is a change of behaviour in datadirect from version 4.2 Workaround is to specify the parameter DateTimeInputParameterType=dateTime in connection string. We need to document this.
Comment by Himavanth R.
16377 | December 05, 2013 06:43:32 AM GMT
Please see the updated document here:
Comment by Frank J.
16378 | February 10, 2014 12:55:46 AM GMT