Filtering DateTime values – SS SLUG Dec 2013 – SQL Brain Bashers - Demo I

This is based on the presentation titled “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This discusses a tricky (or error-prone) filtering issue with datetime values.

Here is the question related to this demo;

You have a table that contains two columns as below. First column type is int and second is datetime.

Id (int)	Date (datetime)
1	2010-06-12 09:17:19.320
2	2010-06-12 23:59:59.997
3	2010-06-13 00:00:00.000
4	2010-06-13 13:30:00.347
5	2010-06-13 23:59:59.997
6	2010-06-14 00:00:00.000
7	2010-06-14 00:00:00.420

You write a query for getting records related only to 2010-06-13;

SELECT Id, Date

FROM dbo.DateTimeValues

WHERE Date BETWEEN '06/13/2010 00:00:00:000' 

AND '06/13/2010 23:59:59:999'

Does this produce the required resultset?

Answer is “No”. Result includes 6th Id too, which is related to 2010-06-14.

The reason for this is, accuracy level of datetime data type and storage mechanism of datetime type. SQL Server only stores time to approximately 1/300th of a second (.00333 seconds), meaning time is stored as;

00:00:00:000
00:00:00:003
00:00:00:007

Because of this, when a value like “2013-12-25 10:30:50:995” is used, it gets rounded up to “2013-12-25 10:30:50:997”. In the query given with the example, “06/13/2010 23:59:59:999” is rounded up to “06/14/2010 00:00:00:000”, resulting 2010-06-14 record in the result.

However, this query can be written in a different way to get the required result;

SELECT Id, Date

FROM dbo.DateTimeValues

WHERE Date >=  '06/13/2010 00:00:00:000' 

    AND Date < '06/14/2010 00:00:00:000'

If you need to accuracy level of datetime for more than 0.00333 seconds, solution is, using datetime2 data type instead datetime. The accuracy of datetime2 data type goes up to 100 nanoseconds.