Between Clause problem in Date comparison – SQL Server

My seniors suggested me not to use the Between clause for Datetime. But whenever i tried to found the reason not to use Between clause nobody gave satisfactory answer. One day i caught in a problem where Between clause was not working. :) I was very happy. you must be thinking that why the man is happy even if his code fails ? This time i got the answer of my question.

Why not to use the Between clause in Date comparison?

I will explain this problem with below example.

Lets create a simple table (Table data type) in  SQL server in which one column is Id column and another is Datetime column.

DECLARE @Temp Table (id INT, val DATETIME)

Insert few Dummy Data in Table

INSERT INTO @Temp VALUES (1,'2010-09-23 00:00:00.000') --Time 12:00 AM
INSERT INTO @Temp VALUES (2,'2010-09-24 00:00:00.000') --Time 12:00 AM
INSERT INTO @Temp VALUES (3,'2010-09-24 01:00:00.000') --Time NOT 12:00 AM
INSERT INTO @Temp VALUES (3,'2010-09-23 11:15:00.000') --Time NOT 12:00 AM

Lets run below Query:

Select * from @Temp
WHERE val BETWEEN '23-Sep-2010' AND '24-Sep-2010' --Bug if Time 12:00 AM , it will be included

And you will get unexpected output here:


id val
----------- -----------------------
1 2010-09-23 00:00:00.000
2 2010-09-24 00:00:00.000
3 2010-09-23 11:15:00.000

As you can see, there is an error in Output. with 23-sep, 24-Sep Date is also coming.

To resolve above problem and if you think only in Between Clause way , you might think to reduce last day by 1 like below query:

Select * from @Temp
WHERE val BETWEEN '23-Sep-2010' AND dateadd(d,-1,'24-Sep-2010')

This means Day between 23-Sep to 23-Sep, which will again not work. It will only display the date which have time as 12:00 AM on 23 Sep.
So, the best solution of above problem is to use the < and > operators like below Query:

Select * from @Temp
WHERE val >= '23-Sep-2010' AND val < '24-Sep-2010'

And the output of above Query:

id val
----------- -----------------------
1 2010-09-23 00:00:00.000
3 2010-09-23 11:15:00.000

The above output looks correct and will work in any scenario. :)

Related posts

  • Muzaffar Shah Khan

    Hi Shiva,

    The above info really very nice!!
    I work as database developer sql 2005
    This is very needful
    Thanks

  • Bijay Singh

    Hi Shiva,

    You have explained the concept very nicely.But I think you have missed one important point.
    I want to give you some input here about the way “BETWEEN” clause is working.
    This syntax for “BETWEEN” clause ,I have taken from http://msdn.microsoft.com/syntax:test_expression [ NOT ] BETWEEN begin_expression AND end_expressionBETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.So when we are using the BETWEEN clause then both the begin_expression and end_expression
    value will be included in the result returned.So when we are executing the query
    SELECT * FROM @Temp  WHERE val BETWEEN ’23-Sep-2010′ AND ’24-Sep-2010
    then It will execute the query like this
    SELECT * FROM @Temp  WHERE val BETWEEN ’23-Sep-2010 00:00:00.000′ AND ’24-Sep-2010 00:00:00.000′
    and it will return the rows like:

    id val

    ———– ———————–

    1 2010-09-23 00:00:00.000

    2 2010-09-24 00:00:00.000

    3 2010-09-23 11:15:00.000

    and whatever result is coming,its correct.I don’t find any bug in that.

    Now if you want to get the data with val  ‘2010-09-23 00:00:00.000′,’2010-09-24 00:00:00.000′ , ‘2010-09-23 11:15:00.000′,’2010-09-24 01:00:00.000′
    we need to use the query as SELECT * FROM @Temp  WHERE val >= ’23-Sep-2010′ AND  val = ’23-Sep-2010 00:00:00.000′ AND  val < '25-Sep-2010 00:00:00.000'
    and it will pull all the value starting from '23-Sep-2010 00:00:00.000'  and ends to

    '24-Sep-2010 23:59:59:999'  and hence we will get the desired result.