Click here to Skip to main content
13,559,193 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 18 Dec 2012
Licenced CPOL

Quick and Easy Date Range Selection

Rate this:
Please Sign up or sign in to vote.
Easy Between replacement.


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

      Foo F
      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.

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.



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

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02-2016 | 2.8.180515.1 | Last Updated 18 Dec 2012
Article Copyright 2012 by George H. Slaterpryce III
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid