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. 🙂

Posted

in

by

Tags:


Related Posts

Comments

2 responses to “Between Clause problem in Date comparison – SQL Server”

  1. Muzaffar Shah Khan Avatar
    Muzaffar Shah Khan

    Hi Shiva,

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

  2. Bijay Singh Avatar
    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.

Leave a Reply to Bijay SinghCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading