Why SQL changes the exact datetime by one tick

Question

DECLARE @dateEnd datetime
SET @dateEnd = '2014-11-30T23:59:59.999'
SELECT @dateEnd

Why do I get result: 2014-12-01 00:00:00.000

I want to use variables for the SELECT condition:

where [MyDate] between @dateStart and @dateEnd

This is another issue but related. I would like the first datapoint (dataStart) to be included and the second (dataEnd) to be excluded from the selected data range. How to do it?

Answer 1

The time range for datetime is '00:00:00 through 23:59:59.997'. At '.999', this value gets rounded up to the nearest second, which happens to be the start of the next day, leading to the results described. For the record though, '.998' will retain the time part at '23:59:59' of the same day.

MSDN

Demo

Answer 2

Precision of Sql Server's DateTime Data Type

Just to elaborate on shree.pat's answer, Sql Server's DATETIME data type has a resolution of 300 'steps' or quanta per 1 second, or a resolution interval of ~3ms.

This means that accurate representation of time instants of a 1 millisecond accuracy are not possible with DATETIME. In the example here, 2014-11-30T23:59:59.999 will be rounded up to 2014-12-01 00:00:00.000. Similarly, 1 millisecond prior, 2014-11-30T23:59:59.998 will be rounded to 2014-11-30T23:59:59.997.

This query demonstrates how DateTime rounds the milliseconds, as compared to the actual time:

WITH cteMS AS
(
    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY o1.object_id) as MilliSeconds
    FROM sys.objects o1 CROSS JOIN sys.objects o2
)
SELECT DATEADD(ms, cteMS.MilliSeconds, CAST('2015-01-01' AS DATETIME2)) AS DT2, 
       DATEADD(ms, cteMS.MilliSeconds, CAST('2015-01-01' AS DATETIME)) AS DT
FROM cteMS;

If you require accuracies of 1 millisecond or better, then you should use the DATETIME2 datatype, which can represent precisions down to 100ns.

The problem with comparing date ranges with BETWEEN

As per Damien's comment, your question illustrates why it is not a good idea to use BETWEEN to search for data which are between two Dates.

The accuracy of your query is dependent on the date type, since where [MyDate] between @dateStart and @dateEnd will now give different results depending on whether @dtStart, @dtEnd and the date and time column you are comparing with.

Instead, use >= on the lower bound (inclusive) and < on an excluded upper bound, like so:

DECLARE @dateEnd AS DateTime, or DateTime2
SET @dateEnd = '2014-11-31';
SELECT ... WHERE [MyDate] >= @dateStart AND [MyDate] < @dateEnd;