Click here to Skip to main content
12,510,860 members (57,324 online)
Click here to Skip to main content
Add your own
alternative version

Stats

3.7K views
5 bookmarked
Posted

Quick and Easy Date Range Selection

, 18 Dec 2012 CPOL
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

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)

Share

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

You may also be interested in...

Pro
Pro

Comments and Discussions

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