Quick and Easy Date Range Selection

Introduction

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 BETWEEN operator. 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 DateDiff.

Using the code

<o>Assume for a moment that you have a table named "Foo" with several columns of data. One of these columns is named "Created".

<o>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)
  as
    Set NoCount On

    Select
      *
    From
      Foo F
    Where
      DateDiff(DD,F.Created,@Start) <= 0
      And DateDiff(DD,F.Created,@End) >= 0

    Return

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.

The first 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.

Enjoy.