tracker issue : CF-3041250

Duplicate ID:	CF-3040487

Problem:

Connecting to SQL Server 2008 via CFQUERY provides data from the next day; whereas, criteria including a timestamp with milliseconds (i.e.  2010-03-07 23:59:59) will provide data with a value of 2010-03-08 00:00:00.0. When submitting the exact query natively in SQL Server Mangemnt Studio, I get the expected results: items from 2010-03-07 only.If I modified the criteria in CFQUERY to not  consider the milliseconds (i.e. 2010-03-07 23:59:00). It will not get results from the next day (2010-03-08).After further tests, I discovered that criteria including milliseconds of 30-59 would provide results from the next day while milliseconds below 30 would provide results as expect (from the same day). This implies that some rounding with CFQUERY is occurring.Testing same code against a datsource on SQL Server 2005 provides working results where the rounding  does not happen.
Method:

Using SQL Server 2008 Enterprise Create a table with field named EventTime (smalldatetime)Enter 5 records for 2010-03-07 00:00:00Enter 10 records for 2010-03-08 00:00:00Enter 10 records for 2010-03-08 16:00:00Using cfquery enter query... select * from tablenamewhere EventTime BETWEEN {ts '2010-03-07 00:00:00'} AND {ts '2010-03-07 23:59:59'} ...15 records will be provided when 5 were expected. 
Result:

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

Watson Bug ID:	3041250

Deployment Phase:	Release Candidate

External Customer Info:
External Company:  
External Customer Name: Robert Mann
External Customer Email: 10813C3D3E88AD24992001AB
External Test Config: 03/31/2010

Attachments:

Comments: