LLI Wiki — My Digital Knowledge Base

Generating a date table with a recursive CTE in SQL Server

T-SQL queries that generate one row per day, month or week for a time dimension.

A recursive common table expression (CTE) can generate a table of dates, for example for a time dimension in a data warehouse. The queries below produce one row per day, then per month, then per week.

One row per day

-- A recursive common table expression (CTE) to create
-- a table with dates within a defined range.
-- Maximum count of dates: 32,747 (below the MAXRECURSION limit of 32,767)

DECLARE @startDate datetime, @endDate datetime;

SET @startDate = {d N'2011-01-01'};
SET @endDate   = {d N'2011-12-31'};

WITH [dates] ([Sequence], [date], [year], [month], [day]) AS
   (SELECT 1 AS [Sequence]
          ,@startDate AS [date]
          ,YEAR(@startdate) AS [year]
          ,MONTH(@startdate) AS [month]
          ,DAY(@startdate) AS [day]
    UNION ALL
    SELECT Sequence + 1 AS Sequence
          ,DATEADD(d, 1, [date]) AS [date]
          ,YEAR(DATEADD(d, 1, [date])) AS [year]
          ,MONTH(DATEADD(d, 1, [date])) AS [month]
          ,DAY(DATEADD(d, 1, [date])) AS [day]
    FROM [dates]
    WHERE [date] < @endDate)

SELECT [Sequence]
      ,[date]
      ,[year]
      ,[month]
      ,[day]
FROM [dates]
OPTION (MAXRECURSION 32747);

One row per month

DECLARE @startDate datetime, @endDate datetime;

SET @startDate = {d N'2011-01-01'};
SET @endDate   = {d N'2011-12-31'};

WITH [dates] ([Sequence], [date], [year], [month]) AS
   (SELECT 1 AS [Sequence]
          ,@startDate AS [date]
          ,YEAR(@startDate) AS [year]
          ,MONTH(@startDate) AS [month]
    UNION ALL
    SELECT Sequence + 1 AS Sequence
          ,DATEADD(m, 1, [date]) AS [date]
          ,YEAR(DATEADD(m, 1, [date])) AS [year]
          ,MONTH(DATEADD(m, 1, [date])) AS [month]
    FROM [dates]
    WHERE [date] < @endDate)

SELECT [Sequence]
      ,[date]
      ,[year]
      ,[month]
FROM [dates]
OPTION (MAXRECURSION 32747);

One row per week

DECLARE @startDate DATETIME, @endDate DATETIME;

SET @startDate = {D N'2014-01-01'};
SET @endDate = {D N'2016-07-01'};

WITH [dates] ([Sequence], [date], [year], [month], [week], [day])
          AS (SELECT 1 AS [Sequence],
                     @startDate AS [date],
                     YEAR(@startDate) AS [year],
                     MONTH(@startDate) AS [month],
                     DATEPART(WEEK, @startDate) AS [week],
                     DAY(@startDate) AS [day]
               UNION ALL
               SELECT Sequence + 1 AS Sequence,
                      DATEADD(WEEK, 1, [date]) AS [date],
                      YEAR(DATEADD(WEEK, 1, [date])) AS [year],
                      MONTH(DATEADD(WEEK, 1, [date])) AS [month],
                      DATEPART(WEEK, DATEADD(WEEK, 1, [date])) AS [week],
                      DAY(DATEADD(WEEK, 1, [date])) AS [day]
               FROM [dates]
               WHERE [date] < @endDate)

SELECT * FROM [dates]
OPTION (MAXRECURSION 32747);

Notes

Mastodon LLIMastodon SocialFosstodon