Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Date Range WHERE Clause Simplification

From Wiki

Jump to: navigation, search

If you're working with date data that includes start & stop times, such as events or activities, querying for events that are active during a date range seems complicated.

Often what seems to make sense at first is to use various ranged conditions put together with OR clauses, such as:

  2.       @FromTime datetime,
  3.       @ToTime datetime
  5.    -- Set values for one entire day
  6.    SET @FromTime = '20080813'
  7.    SET @ToTime = '20080814' -- This value is exclusive of the end point: we want only activities active during the day of 20080813.
  9.    SELECT *
  10.    FROM Activity
  11.    WHERE
  12.       (StartDtm >= @FromTime AND StartDtm < @ToTime) -- starts during the day
  13.       OR (EndDtm >= @FromTime AND EndDtm < @ToTime) -- or ends during the day
  14.       OR (StartDtm < @FromTime AND EndDtm >= @ToTime) -- or spans the day completely, a range easy to miss at first

But wow, six criteria with some complex grouping and booleans. Is there any way to simplify?

A graphical representation of what we're trying to accomplish may help. Here are all the possible combinations of a row in the Activity table to the desired date range. Activity time ranges are represented by |----| and the desired date range is represented by |====|.

  |--before--| |--entering--| |--during--| |--leaving--| |--after--|
                     |=======desired range=======|
                (@FromTime)                  (@ToTime)

So there are six possibilities: an activity is before, entering (crossing the start time), during, leaving, after, or spanning the desired date range.

Looking at it this way, we can try to devise a WHERE clause that will cover the four possibilities we're interested in (all but before or after). But wait a minute. "All but before or after" is exactly what we need, and it's much simpler. Let's try it:

  1. SELECT *
  2.    FROM Activity
  3.    WHERE
  4.       NOT (
  5.          (StartDtm < @FromTime AND EndDtm < @FromTime) -- wholly before
  6.          OR (StartDtm >= @ToTime AND EndDtm >= @ToTime) -- wholly after
  7.      )

This is only four criteria, so we're making progress. We can simplify that to get rid of the NOT:

Given 3 ranges, before, during, and after, we can convert:

  NOT (before OR after)
  -> (NOT before) AND (NOT after)
  -> (during + after) AND (before + during)
  -> during

If you think about these transformations, they should make sense. All of them are valid representations of "during."

  1. SELECT *
  2.    FROM Activity
  3.    WHERE
  4.       (StartDtm >= @FromTime OR EndDtm >= @FromTime) -- works out to NOT before
  5.       AND (StartDtm < @ToTime OR EndDtm < @ToTime) -- works out to NOT after

Finally, we can make one last simplification because we know that EndDtm can never be before StartDtm. You can tell if an activity ended before the date range or starts after the date range with just two criteria. Which means you can tell if activity is active today with just two as well.

  1. SELECT *
  2.    FROM Activity
  3.    WHERE
  4.       StartDtm < @ToTime -- the activity starts before the end of the date range
  5.       AND EndDtm >= @FromTime -- and ends after the start of the date range

Again, some thought should make it clear how this will give the correct results. The realization that EndDtm is always greater than StartDtm makes one of the two criteria on each line redundant.

And there you have an amazingly simple way to search for date ranges.

If it helps, write out on paper the ranges like my diagram above. For each criterion, make an X on all the ranges that are covered by it. Since the operator is AND, only those with two X marks are included.

Contributed by: Emtucifor

Part of SQL Server Programming Hacks

Section Dates

563 Rating: 2.6/5 (118 votes cast)