SQL Server -BETWEEN statement using with DATETIME datatype.

I was using BETWEEN statement with the column that has data type of DATETIME. When debugging or validating data, i found out that the count is mismatch due to the DATETIME.
Ex:

Create table #BETWEENDATETIME (RowNumber INT Identity(1,1), DateTimeTest DateTime)

Insert into #BETWEENDATETIME VALUES ( '2010-07-21 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-21 00:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-20 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-23 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-25 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-26 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-23 16:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-28 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-29 17:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-29 15:11:50.760')
Insert into #BETWEENDATETIME VALUES ( '2010-07-30 10:11:50.760')

From the above table:

SELECT * FROM #BETWEENDATETIME WHERE DateTimeTest BETWEEN '2010-07-21' AND '2010-07-29'

It retrieve the following data:

RowNumber DateTimeTest
1 2010-07-21 17:11:50.760
2 2010-07-21 00:11:50.760
4 2010-07-23 17:11:50.760
5 2010-07-25 17:11:50.760
6 2010-07-26 17:11:50.760
7 2010-07-23 16:11:50.760
8 2010-07-28 17:11:50.760

The above retrieved data was invalid.

Solution:

SELECT * FROM #BETWEENDATETIME WHERE DATEADD(Day, DATEDIFF(Day, 0, DateTimeTest), 0) BETWEEN '2010-07-21' AND '2010-07-29'

This returns the following data:

RowNumber DateTimeTest
1 2010-07-21 17:11:50.760
2 2010-07-21 00:11:50.760
4 2010-07-23 17:11:50.760
5 2010-07-25 17:11:50.760
6 2010-07-26 17:11:50.760
7 2010-07-23 16:11:50.760
8 2010-07-28 17:11:50.760
9 2010-07-29 17:11:50.760
10 2010-07-29 15:11:50.760

i.e. we are using the DATEADD(Day, DATEDIFF(Day, 0, DateTimeTest), 0) condition to round the DATETIME value to DATE.

try this for rounding values:

SELECT GETDATE()
SELECT DATEADD(D, DATEDIFF(D, 0, GETDATE()), 0)
SELECT DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0)
SELECT DATEADD(YYYY, DATEDIFF(YYYY, 0, GETDATE()), 0)
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)

I hope this article helps you!!!..
Thank you. :)

My First Post.....

Hi everyone!!!... This is my first post. I thought many times to create a blog to share my experiences. May be i was busy or lazy, but i couldn't start until today. Hopefully with full energy i started my blog MSTECHSOLUTIONS2010. I will update my blog with new item with simple to complex problems with solutions.