Does BETWEEN dates work on SQL SERVER 2008?

Question

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

He claims that BETWEEN the date gives conflicting results.

From my 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 

Note that the S2 response in the second query shows "Out" when it is clear that the date should be.

According to cdonner , the reason for this is because:

[least significant digit of type DateTime in SQL is 3 ms

I think the reason is much simpler. I think this is because he used strings, not dates, in his query.

I apologize for my SQLServer-ish. I mainly talk about Oracle, so it can be ugly. But when I take his request that -proves- there is a problem 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 

What is right?

NB: this is not an attempt to resolve a dispute or start a fiery war. I really want to understand if SQL Server BETWEEN is less functional than Oracle BETWEEN. We do not have such a problem 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 

Here you are comparing strings, not dates.

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

Answer 2

I need 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 the correct BETWEEN datetime

Answer 3

The SQL server stores date and time values ​​as a number. For example, 0 - 1900-01-01 00: 00: 00.000

The example that you give in your question depends on rounding issues, just as a floating point value of 1.0 is stored as 0.99999 ...

To accurately compare dates, you must specify the value in a date and time type, and then perform the 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 

This will produce the expected result: s1 == in, s2 == in

Answer 4

You are correct that his code is erroneous due to string comparison.

However, if you use the datetime type and not the new date type, it does not matter. The reason is that you usually don’t want an inclusive search anyway, and not as a code:

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

you usually write it like this:

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

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

I expect the problem to be fixed for the new Date type, but I do not have SQL Server 2008, so I can not test it.

Answer 5

Because he is completely wrong - he compares strings

if you pass them to datetime or replace them with date variables that 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 6

The code on the blog was nonsense, and I should mark it. The problem exists. This has nothing to do between BETWEEN, but with rounding (which can lead to the fact that BETWEEN does not work under certain circumstances). While SQL Server is doing the round, .NET is not working, and I ran into problems with multiple inserts in a very short amount of time, when I assumed that I have different datetime values, but due to rounding they became the same in the database data. The message was fixed a long time ago, and the code examples still work on SQL Server 2008.

If you run this query, you will see what I had in mind:

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

Answer 7

Just never use BETWEEN with datetime values. In MySQL, I can do

 created >= CURDATE() - INTERVAL 1 DAY AND created < CURDATE() 

to limit the creation time (total).

With BETWEEN '2011-05-02' AND '2011-05-02 23:59:59 "I will gamble with a resolution of one second, and I skipped the created one." 2011-05-02 23:59 :. 59,001 '

Answer 8

Very helpful discussion!

I had similar issues resolved with CAST and CONVERT, as in:

cast (CONVERT (varchar (8), [PostDate], 112) AS DATE) BETWEEN "2013-01-01" AND "2013-01-31"

Works like a charm, verified with a few detailed checks.

"Cast (" is required (believe me, I have bald spots to prove it). But there was no need to declare variables, etc.

There would be an interest in feedback if I miss the boat using the method or my answer.

Thanks!