We've all been there:
Writing some SQL script or procedure that selects data from tables based on a given date range. There are so many ways to skin this particular cat but the method
described here is my favorite.
The most common way I've seen people do a date range select is through the
Generally this causes a problem because of the time portion of the datetime passed in.
To solve this, instead of modifying the passed in dates time portion, we just use
Using the code
Assume for a moment that you have a table named "Foo" with several columns of data. One of these columns is named "Created".
You now want to pull everything from this table for a given date range. So we'll use two parameters for the stored procedure. @Start, and @End.
Create Procedure ListEverythingFromFooBetween(@Start DateTime, @End DateTime)
Set NoCount On
DateDiff(DD,F.Created,@Start) <= 0
And DateDiff(DD,F.Created,@End) >= 0
Nice and simple: No need to worry about time portions or converting datetimes to just date types, or even having to break the input parameters into component day, month, year variables.
DateDiff will limit the list to anyting that is AFTER or ON the start date, and the second
DateDiff will limit the list to anything that is BEFORE
or ON the end date.
You now have a "Between" that doesn't care about time.