tracker issue : CF-3497870

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)

Attachments:

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

Comments: