Does BETWEEN with dates actually work in SQL SERVER 2008

Question

According to cdonner, in his answer here and on his blog.

He claims that BETWEEN with date yields inconsistent results

From his blog:

select
    case when '9/1/08' between '9/1/08' and '9/15/08'
     then 'in' else 'out' end as s1,
    case when '9/1/08' between '8/28/08' and '9/1/08'
     then 'in' else 'out' end as s2

s1   s2
---- ----
in   in

(1 row(s) affected)
select
    case when '1/1/08' between '1/1/08' and '2/1/08'
     then 'in' else 'out' end as s1,
    case when '1/1/08' between '12/31/07' and '1/1/08'
     then 'in' else 'out' end as s2

s1   s2
---- ----
in   out

(1 row(s) affected

Notice that the S2 answer in the second query show "Out" when clearly the date should be in.

According to cdonner, the reason this is so is because:

[the] least significant digit of the DateTime type in SQL is 3 msec

I think the cause is much simpler than that. I think it's because he's using strings and not dates in his query.

Please excuse my SQLServer-ish. I speak mainly Oracle, so this may be ugly. But when I take his query that -proves- there's an issue and replace his strings with datetime variables I get the correct output.

DECLARE @Jan108 datetime
DECLARE @Feb108 datetime
DECLARE @Dec3107 datetime

SET @Jan108 = '1/1/08'
SET @Feb108 = '2/1/08'
SET @Dec3107 = '12/31/07'

select
    case when @Jan108 between @Jan108 and @Feb108
     then 'in' else 'out' end as s1,
    case when @Jan108 between @Dec3107 and @Jan108
     then 'in' else 'out' end as s2

Which is correct?

NB: this isn't an attempt to settle an argument or to start a flame war. I truly want to understand if SQL Server BETWEEN is less functional than the Oracle BETWEEN. WE have no such issue in Oracle.

Answer 1

In Oracle:

select
    case when '1/1/08' between '1/1/08' and '2/1/08'
        then 'in' else 'out' end as s1,
    case when '1/1/08' between '12/31/07' and '1/1/08'
        then 'in' else 'out' end as s2
FROM dual

in out

You are comparing strings here, not dates.

There is nothing BETWEEN 12/31/07 and 1/1/08, as 2 goes after / in ASCII

Answer 2

You are correct that his code is flawed due to string comparisons.

However, if you're using a datetime type rather than the new date type it doesn't matter. The reason is that you don't typically want an inclusive search anyway, and so rather than code like this:

SELECT * FROM [MyTable] WHERE MyDateColumn BETWEEN @StartDate AND @EndDate

you'd normally write it like this:

SELECT * FROM [MyTable] WHERE MyDateColumn >= @StartDate AND MyDateColumn < @EndDate

where @EndDate is actually one greater than the day you really want.

I expect the problem is fixed for the new Date type, but I don't have SQL Server 2008 handy so I can't test it.

Answer 3

I have to use something like this:

Declare @BeginDate SmallDateTime
Declare @EndDate SmallDateTime
Set @BeginDate = '2007-08-01'
Set @EndDate = '2007-08-31'

Select *
From dbo.table1 a
Where a.session_date Between @BeginDate + ' 00:00:00' And @EndDate + ' 23:59:59'
Order By a.session_date asc

To get correct BETWEEN datetime's

Answer 4

Because he's totally wrong - it's comparing strings

if you cast them to a datetime or replace them with date variables it works:

select
    case when CAST('JAN 01 2008' as smalldatetime)
     between CAST('JAN 01 2008' as smalldatetime)
     and CAST('FEB 01 2008' as smalldatetime)
     then 'in' else 'out' end as s1,
    case when CAST('JAN 01 2008' as smalldatetime)
     between CAST('DEC 31 2007' as smalldatetime)
     and CAST('JAN 01 2008' as smalldatetime)
     then 'in' else 'out' end as s2

Answer 5

SQL server stores datetime values as a number. For instance, 0 is 1900-01-01 00:00:00.000

The example you give in your question is subject to rounding problems, similar to how the floating-point value 1.0 is stored as 0.99999...

To accurately compare dates, you would cast the value to a datetime type and then do your comparison.

SELECT
CASE 
 WHEN cast('1/1/08' as datetime) 
        BETWEEN cast('1/1/08' as datetime) AND cast('2/1/08' as datetime) 
 THEN 'in' ELSE 'out' 
END AS s1,
CASE 
 WHEN cast('1/1/08' as datetime) 
        BETWEEN cast('12/31/07' as datetime) AND cast('1/1/08' as datetime) 
 THEN 'in' ELSE 'out' 
END AS s2

Which will result in your expected output: s1==in, s2==in

Answer 6

The code on the blog was nonsense, and I owe Mark for pointing that out to me. The issue exists. It has nothing to do with BETWEEN, but with rounding (which can cause BETWEEN to fail in certain circumstances). While SQL Server does round, .Net does not, and I ran into issues with multiple inserts in a very short timeframe where I assumed that I had distinct datetime values, but due to rounding they became the same in the database. The post was corrected a long time ago, and the code examples still work on SQL Server 2008.

If you run this query, you will see what I was referring to:

select convert(varchar(32), convert(datetime,
                            '9/1/08 00:00:00.005'), 121);