SQL Server 2008 BETWEEN function

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 I supplied here is 1 msec later than the range of the BETWEEN function, yet on my SQL 2008 server instance, the result is 'is between' and not the expected 'not between'...

Is this a bug, or is it a necessary compromise in the design for some reason I'm not seeing?

And yes, as soon as I add even a fraction of a second beyond midnight of 2016-01-01, I get the expected 'not between' result.

Answer 1

Look at documentation for datetime type. In section Rounding of datetime Fractional Second Precision you will find the explanation to your issue:

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 is between. Unfortunatelly this is how datetime data type works, there is not much you can do about this, unless you can use other data type, like datetime2.

Answer 2

If you use the code below, you'll see that the problem is the datetime type. Look at the documentation about datetime and your doubt will be solved!

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