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
OPTION (MAXRECURSION n)sets the maximum number of recursion levels. Without this option the default is 100 levels, so a longer date range fails.- The examples use 32,747, a value below the maximum of 32,767.