SQL: generate date/time series

Submitted by st on

There are many examples of user defined table functions generating the series of dates(times) between two specified values. Usually, in OLAP you can see a table filled by the date/time series, too. Such table avoid to generate the values "on the fly".

However, in some cases you may be unable to use an UDF or a table. Fortunately, "pure SQL" method still works.

Suppose, the start and finish dates are specified as entry parameters. The transact SQL code below generates the series "moth by month". 

WITH date_limits (min_date_value, max_date_value) AS
(
  SELECT convert(date, '20100101'),
         convert(date, '20110201')
)
,
date_series (date_value, num) AS
(
  SELECT min_date_value AS date_value, 1
  FROM date_limits
  UNION ALL
  SELECT dateadd(month, 1, date_series.date_value), num + 1
  FROM date_limits INNER JOIN date_series 
         ON date_series.date_value < date_limits.max_date_value
)
SELECT date_value, num
FROM date_series

Result

date_value num
---------- -----------
2010-01-01 1
2010-02-01 2
2010-03-01 3
2010-04-01 4
2010-05-01 5
2010-06-01 6
2010-07-01 7
2010-08-01 8
2010-09-01 9
2010-10-01 10
2010-11-01 11
2010-12-01 12
2011-01-01 13
2011-02-01 14

Here is the real world OLAP example: transposing data values with their periods by month.

CREATE TABLE #t (
  date_from date,
  date_to date,
  value int
)
GO
INSERT INTO #t VALUES
('20100101', '20100301', 1),
('20100401', '20100501', 2),
('20100501', '20100901', 3)
GO
SELECT * FROM #t;
 
WITH date_limits (min_date_value, max_date_value) AS
(
  SELECT min(date_from), max(date_to)
  FROM #t
)
,date_series (date_value, num) AS
(
  SELECT min_date_value AS date_value, 1
  FROM date_limits
  UNION ALL
  SELECT dateadd(month, 1, date_series.date_value), num + 1
  FROM date_limits 
       INNER JOIN date_series 
       ON date_series.date_value < date_limits.max_date_value
)
SELECT ds.date_value, t.value
FROM date_series ds 
     INNER JOIN #t t
     ON ds.date_value BETWEEN t.date_from AND t.date_to
GO
 
DROP TABLE #t
GO

Result

date_from  date_to    value
---------- ---------- -----------
2010-01-01 2010-03-01 1
2010-04-01 2010-05-01 2
2010-05-01 2010-09-01 3
 
date_value value
---------- -----------
2010-01-01 1
2010-02-01 1
2010-03-01 1
2010-04-01 2
2010-05-01 2
2010-05-01 3
2010-06-01 3
2010-07-01 3
2010-08-01 3
2010-09-01 3