SQL Server 2008 BETWEEN Feature

Question

Here is my code:

DECLARE @d1 DATETIME DECLARE @d2 DATETIME SET @d1 = '2015-01-01 00:00:00' SET @d2 = '2015-12-31 23:59:59.999' SELECT CASE WHEN ('2016-01-01 00:00:00' BETWEEN @d1 AND @d2) THEN 'is between' ELSE 'not between' END AS BetweenOrNotBetween 

The date specified here is 1 ms later than the range of the BETWEEN function, but on my SQL Server 2008 instance, the result is 'between' , not expected > 'not between' ...

Is this a mistake, or is it a necessary compromise in design for some reason I don’t see?

And yes, as soon as I add at least half a second after midnight 2016-01-01, I get the expected result 'not between .

Answer 1

See the documentation for the datetime type . In the Rounding of datetime Fractional Second Precision section, you will find an explanation of your problem:

datetime values ​​are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

This means that if you have 1 millisecond, it is actually rounded to 0 milliseconds, so your result is between . Unfortunately, this is how the datetime data type works, you cannot do this unless you can use another data type, such as datetime2 .

Answer 2

If you use the code below, you will see that the problem is with the datetime type. Check out the datetime documentation and your doubts will be resolved!

 DECLARE @d1 datetime2 DECLARE @d2 datetime2 SET @d1 = '2015-01-01 00:00:00.00' SET @d2 = '2015-12-31 23:59:59.999' SELECT CASE WHEN ('2016-01-01 00:00:00.00' BETWEEN @d1 AND @d2 ) THEN 'is between' ELSE 'not between' END AS BetweenOrNotBetween