Wednesday, July 19, 2006

Need a date range in SQL without filling a table?

It's evidentally SQL week here at Chez Brooks. Today I needed a really high performance query to deliver a date range table between two dates. Simple, and there seem to be tons of variants out there, but I didn't like the query plans of any of them. The resulting query below works for any start date and end date pair, and will return a date between two given dates.

DECLARE @LowDate DATETIME
SET @LowDate = '01-01-2006'

DECLARE @HighDate DATETIME
SET @HighDate = '12-31-2016'

SELECT DISTINCT DATEADD(dd, Days.Row, DATEADD(mm, Months.Row, DATEADD(yy, Years.Row, @LowDate))) AS Date
FROM
(SELECT 0 AS Row UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
 UNION ALL SELECT 20 -- add more years here...
) AS Years
INNER JOIN
(SELECT 0 AS Row UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
 UNION ALL SELECT 10 UNION ALL SELECT 11
) AS Months
ON DATEADD(mm, Months.Row,  DATEADD(yy, Years.Row, @LowDate)) <= @HighDate 
INNER JOIN
(SELECT 0 AS Row UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
 UNION ALL SELECT 30
) AS Days
ON DATEADD(dd, Days.Row, DATEADD(mm, Months.Row,  DATEADD(yy, Years.Row, @LowDate))) <= @HighDate
WHERE DATEADD(yy, Years.Row, @LowDate) <= @HighDate
ORDER BY 1

Some notes on this:

  1. If we assume a start date of January 1st, we need to add at most 11 months and at most 30 days to bump to then end of the year, so that's where the 0-11 months and 0-30 days come from.
  2. Due to the fact that some months have less than 31 days, we can conceivably generate the same date twice. By adding one month and 28 days to January 31st we get the same date as adding zero days and two months; either way the result is March 1st. Thus we need the DISTINCT operator.
  3. If you don't care about the dates being in order, delete the ORDER BY clause.
  4. I've currently limited it to a 20 year range, but you can change that in the subquery that generates the years quite easily.
  5. Doing the DATEADD in the order of year, then month, then days is very important as it insure that the correct leap-day rule is followed.

6 comments:

Anonymous said...

Very useful. Cheers!

Tom Øyvind Hogstad said...

I think my method is easier to read ...

DECLARE @LowDate DATETIME
SET @LowDate = '2006-01-01' -- Use iso 8601 for "datestrings"

DECLARE @HighDate DATETIME
SET @HighDate = '2016-12-31'
;
With Dates(MyDate)
AS
(
Select @LowDate MyDate
UNION ALL
SELECT (MyDate+1) MyDate
FROM Dates
WHERE
MyDate < @HighDate
)
SELECT MyDate FROM Dates
OPTION(MAXRECURSION 0)

And theres really not much of a speed difference :-)

IDisposable said...

However your version only works with SQL Server 2005 (because of the Common Table Expressions), whereas mine works with just about any SQL engine (including Oracle, DB2, Informix, MySQL, and SQL Server 2000).

Phillip said...

Thanks Mark and Tom,

With just a little bit of tinkering, I got a min and max date out of my dataset, created a temp table through these dates, and then inner joined to it on mydate between the startdate and enddate of each record in the dataset.

Really made it simple to turn a large dataset with date ranges into an even larger dataset with a record for each date.

Karl said...

Nice Post, very helpfull.

Karl

not Karl said...

Nice post, very helpful.