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