Why does SQL change exact time-time by one tick

Question

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

Why am I getting the result: 2014-12-01 00:00:00.000

I want to use variables for the SELECT clause:

where [MyDate] between @dateStart and @dateEnd

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

Answer 1

The time range for datetimeis '00: 00: 00 - 23: 59: 59.997 '. In '.999' this value is rounded to the nearest second, which is probably the beginning of the next day, which leads to the described results. For the record, however, “.998” will save the temporary part at “23: 59: 59” on the same day.

MSDN

Demo

Answer 2

Sql Server DateTime

shree.pat , Sql Server DATETIME 300 , ~ 3 .

, 1 DATETIME. 2014-11-30T23:59:59.999 2014-12-01 00:00:00.000. , 1 , 2014-11-30T23:59:59.998 2014-11-30T23:59:59.997.

, DATETIME :

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;

1 , DATETIME2, 100ns.

BETWEEN

Damien, , BETWEEN , .

, where [MyDate] between @dateStart and @dateEnd , @dtStart, @dtEnd , .

>= () < , :

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