SQL Date Ranges
time to read 4 min | 750 words

When you're working with SQL, it's often best to work with sets of data, but when you're working with dates, it's not always possible. Especially if you need to work with date ranges.

For instance, how do you find out how many hours each employee worked per day over the last month? Well, you can have a BusinessDays table, which you can join against, but often you will want to so some ad-hoc date ranges, or you don't have a BusinessDays table and you don't want to create one.

I was playing with ideas about generating sets from start - end values and I finally came up with this function:

CREATE FUNCTION DateRange(@start DATETIME, @end DATETIME)

RETURNS TABLE

AS

RETURN

(

      WITH DateRange(CurrentDate) AS

      (

            SELECT @start as CurrentDate

            union all

            SELECT DATEADD(day,1,CurrentDate)

            FROM DateRange

            WHERE CurrentDate < @end

      )

      SELECT CurrentDate FROM DateRange

);

As you can see, it's using Common Table Expression, so it's 2005 only, but the nice thing about it is that it takes two dates, and return all the dates between them (inclusive).

Some words of caution:

  • I tested it on a highly loaded machine, and it's not the most light-wieght solution in the world. For ten years period, it took over 15 seconds to run.
  • To compare, I inserted the results to a temp table and selected that. I got the results instantly.
  • For dates that are further than 100 days apart, you need to specify high range of MAXRECURSION, often more than I'm comportable with.

Usage:

SELECT

 CurrentDate FROM DateRange('20010101','20120901')
OPTION(MAXRECURSION 10000)

I'm still looking for a better alternative, though.

Tweet Share Share 0 comments
Tags: