4
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?

lante
  • 7,192
  • 4
  • 37
  • 57
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 2
    Which dbms? (Certainly not ANSI/ISO SQL standard.) – jarlh Jan 07 '15 at 11:54
  • 5
    Sql Server DateTimes only have [resolution of 300 quanta](http://stackoverflow.com/a/715484/314291) per second. Switch your data type if you need higher resolution. – StuartLC Jan 07 '15 at 11:54
  • @jarlh is this MS [sql-server] syntax. – Mureinik Jan 07 '15 at 11:56
  • @StuartLC Does the 300 quanta per second resolution lead to the time range described above? I'm not really sure my answer is entirely correct, having gone through the link. – shree.pat18 Jan 07 '15 at 12:01
  • @StuartLC Thanks. I've deleted my answer since I am not convinced it was correct, even if .999 rounding up to the nearest second will cause OP's results. Perhaps you'd care to add your comment as an answer? – shree.pat18 Jan 07 '15 at 12:13
  • 2
    @shree.pat18 . . .Your answer is basically correct and you should undelete it. The documentation says that values are "rounded", so perhaps 23:59:59.998 won't "round up", but 23:59:59.999 would round up. – Gordon Linoff Jan 07 '15 at 12:14
  • 2
    If you can't change data type, change your WHERE condition- WHERE MyDate >= @dateStart AND MyDate < dateEnd – Vishal Gajjar Jan 07 '15 at 12:15
  • @GordonLinoff I see. I should add in this explanation then, because .998 indeed left it at 23:59:59 – shree.pat18 Jan 07 '15 at 12:15
  • seems we cannot user '@' + dateEnd in comment!!! – Vishal Gajjar Jan 07 '15 at 12:16
  • @StuartLC Yep, already did that, and updated my demo with the case for .998 as a counter-example! – shree.pat18 Jan 07 '15 at 12:18
  • possible duplicate of [Why is SQL Server losing a millisecond?](http://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond) – Nate S. Jan 07 '15 at 12:29
  • 2
    It's almost always a better idea to start using semi-open intervals (so use `>=` and `<` rather than `between`) for querying continuous data like datetimes. It's usually easier to compute the start and end values (they tend to be simpler), and you won't end up making double-counting or non-counting errors for values close to the end values. – Damien_The_Unbeliever Jan 07 '15 at 13:32

2 Answers2

8

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

shree.pat18
  • 21,449
  • 3
  • 43
  • 63
3

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;
StuartLC
  • 104,537
  • 17
  • 209
  • 285