Selecting a Date Range

Many beginners do not realize that when they select a date range, they are often omitting the entire ending date. This is because the SQL BETWEEN function spans from 12:00:00 AM of the Start Date – 12:00:00 AM of the End Date. I ran across this problem when trying to do this:

 

Create Procedure sp_GetMessagesByDate(

@StartDate DATETIME,

@EndDate DATETIME)

AS

SELECT Title, Owner

FROM Messages

WHERE CreatedDate Between @StartDate AND @EndDate;

Although the Syntax is correct and the stored procedure ran fine, I noticed that I was getting no data for the End Date even though I new the table contained messages that should have shown up. After some reading and researching, I tried this:

Create Procedure sp_GetMessagesByDate(

@StartDate DATETIME,

@EndDate DATETIME)

AS

SELECT Title, Owner

FROM Messages

WHERE CreatedDate > =  @StartDate AND CreatedDate <= @EndDate;

I thought this would do it, but once again, the @EndDate value is (by default) 12:00:00 A.M….so I still lost the last day.

I finally fixed it with this modification:

Create Procedure sp_GetMessagesByDate(

@StartDate DATETIME,

@EndDate DATETIME)

AS

SELECT Title, Owner

FROM Messages

WHERE CreatedDate Between @StartDate AND DATEADD(d,1,@EndDate;)

This used the DateAdd function to add the additional 24 hours that was needed to get the full end date. This is technically not 100% correct, because by adding a day, you are including 12:00:00 of the day AFTER the end date. In other words, you are picking up an extra millisecond after the end date. There are very few times that this would make a difference in any but the most critical time-sensitive applications. If this one millisecond is going to cause problems, there are more advanced ways to get exactly the date range, but for most uses, this is perfect.