TSQL: Date BETWEEN Query - Ignoring Time

Question

I am trying to do a query between 2 dates. I would like to do it without having to worry about the time. When a user enters the 2 dates they want to search on, there is no selection for time. This means that the dates that they enter default to 12:00 AM.

The dates in the table do have times though. I just would like to ignore the times all together so the search brings back any records form said date range.

Here is my SQL:

TheDate BETWEEN @EnteredBeginDate AND @EnteredEndDate

So when a user does a range search between 8/6/2009 AND 9/9/2009 I want to return the records:

8/6/2009 11:33:02 AM
8/6/2009 11:39:17 AM
9/9/2009 8:21:30 AM

What's happening now is I only get back:

8/6/2009 11:33:02 AM
8/6/2009 11:39:17 AM

Can someone please recommend the best way to do this in SQL? I know how to do it in C#.

Solution 1

Just use DATEADD for the enddate to set it to midnight on the NEXT day...

TheDate BETWEEN @EnteredBeginDate AND DATEADD(day, 1, @EnteredEndDate)

If you want to be really precise, you could subtract a second or millisecond from that to make it 11:59:59 on your specified date:

TheDate BETWEEN @EnteredBeginDate AND DATEADD(second, -1, (DATEADD(day, 1, @EnteredEndDate)))

Solution 2

If you're on SQL Server 2008 or 2008 R2, you could use the new DATE datatype:

TheDate BETWEEN CAST(@EnteredBeginDate AS DATE) AND CAST(@EnteredEndDate AS DATE)

That strips off the time portion and looks only at the date

Solution 3

If you are using SQL Server 2008, then do this:

TheDate BETWEEN cast(@EnteredBeginDate as date) AND cast(@EnteredEndDate as date)

Solution 4

TheDate BETWEEN @EnteredBeginDate AND dateadd(day, 1, @EnteredEndDate)

View more solutions

Comments

[marc_s]

If you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

[Jared]

This will be my chosen answer. +1 for the precise option. Thanks everyone!

[Jared]

This is great. I have to use 2005 though.

[Caltor]

duplicate of earlier answer by marc_s stackoverflow.com/a/8478056/470014

[Caltor]

You need to CAST TheDate rather than the Entered Dates. So it should be CAST(TheDate AS DATE) BETWEEN @EnteredBeginDate AND @EnteredEndDate

[Caltor]

If you want to be REALLY REALLY precise you could cast to datetime2 and subtract one hundred nanoseconds ;) :) TheDate BETWEEN @EnteredBeginDate AND DATEADD(nanosecond, -100, DATEADD(day, 1, CAST(@EnteredEndDate AS datetime2)))