SQL: Turn a Date Range into a List of Dates

I’m working on a project today that deals with date ranges being stored in SQL Server. Imagine a row in a table with a starting and an ending date. My desired output is to transform each single row into several, one for each date within the given date range.

Since I couldn’t find an example exactly like what I needed, I felt I should share what I came up with. I’ll let the code speak for itself. It uses a recursive Common Table Expression (CTE).

The caveats are: you have to run this on SQL Server 2005 or newer to use the WITH keyword, and if any individual date range is more than 100 days, you’ll get an error by default [you can use OPTION (MAXRECURSION value) to increase that limit]. UPDATE: I’ve added a WHERE clause to prevent the error related to the default recursion limit. This change will leave out date ranges larger than 100 days, which is fine for me because anything larger than two weeks would be a data entry error in my situation.

DECLARE @dateranges TABLE (range_id VARCHAR(2), date_begin DATETIME, date_end DATETIME)
INSERT @dateranges SELECT 'A', '2010-01-01', '2010-01-03'
INSERT @dateranges SELECT 'B', '2008-02-27', '2008-03-01'
INSERT @dateranges SELECT 'C', '2010-04-26', '2010-04-26'
INSERT @dateranges SELECT 'D', '2000-02-01', '2003-02-20'

;WITH cte (id, d)
     AS (SELECT tbl.range_id AS id
                ,tbl.date_begin AS d
           FROM @dateranges tbl
          WHERE DATEDIFF(DAY, tbl.date_begin, tbl.date_end) <= 100
         UNION ALL
         SELECT tbl.range_id AS id
                ,DATEADD(DAY, 1, cte.d) AS d
           FROM cte
                INNER JOIN @dateranges tbl
                  ON cte.id = tbl.range_id
          WHERE cte.d < tbl.date_end)
SELECT id AS range_id
       ,d AS date_within_range
  FROM cte
 ORDER BY id, d

You start with this:

and end up with this:

Notice that “D” is filtered out of these results.

Tags: cte, recursive, sql