Click here to Skip to main content
Click here to Skip to main content

Quick and Easy Date Range Selection

, 18 Dec 2012
Rate this:
Please Sign up or sign in to vote.
Easy Between replacement.

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

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)
  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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

George H. Slaterpryce III
Architect
United States United States
Developing software since 1995, starting out with Borland Turbo C++ 3 & Motorolla Assembly.
Eventually learning several different languages such as
Pascal/Delphi,Basic, Visual Basic,FoxPro, ASP, PHP, and Perl.
 
Currently using the .net framework for most development task.
 
Hobbies include underwater robotics, and electronics.
 
George's current language of choice is C#.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 18 Dec 2012
Article Copyright 2012 by George H. Slaterpryce III
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid