Generate a Date Range: List All Dates Between Two Dates

To generate a date range in SQL Server, number the days between two dates. Add each number to the start. A recursive CTE does that on every version since SQL Server 2005. GENERATE_SERIES does it in one line on SQL Server 2022 and later.

The Recursive CTE Version

A common table expression, or CTE, is a named query written at the top of a statement. A recursive CTE refers to itself. The first part, the anchor, returns the start date. The second part adds one day to the previous row and repeats while the day is before the end date. The result is one row per day, both end dates included. A few lines are enough to generate a date range this way.

DECLARE @StartDate date = '2021-11-01', @EndDate date = '2021-12-01';
WITH Days AS (
    SELECT @StartDate AS Day
    UNION ALL
    SELECT DATEADD(DAY, 1, Day) FROM Days WHERE Day < @EndDate
)
SELECT COUNT(*) AS DayCount, MIN(Day) AS FirstDay, MAX(Day) AS LastDay FROM Days;

The script counts the rows instead of listing them, so the output stays short. Remove the aggregates and select Day from Days to see every date. November has 30 days, and the range runs to December 1, so the count is 31. The variables use the date type, so no time part gets in the way of the comparison.

The same idea works for months. Change DAY in DATEADD to MONTH and the step changes with it. A range that starts on January 31 runs 2021-01-31, 2021-02-28 and 2021-03-28. The day stays stuck at the 28th, because each step adds a month to the previous row. Add months to the start date instead when the day matters. The condition in the recursive part has to compare against the end value, or the recursion never stops. The limit of 100 recursions protects you from exactly that mistake.

The Limit of 100 Recursions

Try the same script for a whole year. It stops with an error, and no row comes back.

DECLARE @StartDate date = '2021-01-01', @EndDate date = '2021-12-31';
WITH Days AS (
    SELECT @StartDate AS Day
    UNION ALL
    SELECT DATEADD(DAY, 1, Day) FROM Days WHERE Day < @EndDate
)
SELECT COUNT(*) AS DayCount FROM Days;

Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

SQL Server limits a recursive CTE to 100 recursions by default. The anchor row is the first row, and each recursion adds one more. So a range of 101 days works and a range of 102 days fails. The next script proves both edges.

DECLARE @StartDate date = '2021-01-01', @EndDate date = '2021-04-11';
WITH Days AS (
    SELECT @StartDate AS Day
    UNION ALL
    SELECT DATEADD(DAY, 1, Day) FROM Days WHERE Day < @EndDate
)
SELECT COUNT(*) AS DayCount FROM Days;
GO
DECLARE @StartDate date = '2021-01-01', @EndDate date = '2021-04-12';
WITH Days AS (
    SELECT @StartDate AS Day
    UNION ALL
    SELECT DATEADD(DAY, 1, Day) FROM Days WHERE Day < @EndDate
)
SELECT COUNT(*) AS DayCount FROM Days;

The hint MAXRECURSION raises the limit. A value of 0 removes it, and that works. It also removes the protection against a faulty condition, which can then loop for a long time. Use a number that fits the longest range you expect, with some room. The script below uses 400 and returns a full year, and a leap year too.

DECLARE @StartDate date = '2021-01-01', @EndDate date = '2021-12-31';
WITH Days AS (
    SELECT @StartDate AS Day
    UNION ALL
    SELECT DATEADD(DAY, 1, Day) FROM Days WHERE Day < @EndDate
)
SELECT COUNT(*) AS DayCount FROM Days OPTION (MAXRECURSION 400);
GO
DECLARE @StartDate date = '2024-01-01', @EndDate date = '2024-12-31';
WITH Days AS (
    SELECT @StartDate AS Day
    UNION ALL
    SELECT DATEADD(DAY, 1, Day) FROM Days WHERE Day < @EndDate
)
SELECT COUNT(*) AS DayCount FROM Days OPTION (MAXRECURSION 400);

GENERATE_SERIES in One Line

SQL Server 2022 added GENERATE_SERIES, which returns a list of numbers. It can generate a date range without any recursion. Add each number to the start date. The function needs a database at compatibility level 160 or higher, and it has no recursion limit. This version returns the full year.

DECLARE @StartDate date = '2021-01-01', @EndDate date = '2021-12-31';
SELECT COUNT(*) AS DayCount, MIN(DATEADD(DAY, value, @StartDate)) AS FirstDay, MAX(DATEADD(DAY, value, @StartDate)) AS LastDay
FROM GENERATE_SERIES(0, DATEDIFF(DAY, @StartDate, @EndDate));

A Version for Older Servers

Without GENERATE_SERIES, build the list of numbers from a system view. Cross join sys.all_objects to itself, number the rows with ROW_NUMBER, and keep the first rows that the range needs. The result is the same year, and the script has no recursion to limit.

DECLARE @StartDate date = '2021-01-01', @EndDate date = '2021-12-31';
SELECT COUNT(*) AS DayCount, MIN(Day) AS FirstDay, MAX(Day) AS LastDay
FROM (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
           DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, @StartDate) AS Day
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
) AS d;

Leave Out the Start and the End

The BETWEEN operator includes both end points, and so do the scripts above. To leave both out, start the numbers at 1 and stop one day early.

DECLARE @StartDate date = '2021-11-01', @EndDate date = '2021-12-01';
SELECT COUNT(*) AS DayCount, MIN(Day) AS FirstDay, MAX(Day) AS LastDay
FROM (SELECT DATEADD(DAY, value, @StartDate) AS Day FROM GENERATE_SERIES(1, DATEDIFF(DAY, @StartDate, @EndDate) - 1)) AS d;

Dates With a Time Part

Sometimes the start and end carry a time, and you want periods of one day, with a short last period. Use datetime2 for the variables. Each period starts a whole number of days after the start. It ends one day later, or at the end time when that comes first. The WHERE clause drops an empty period at the end.

DECLARE @Start datetime2(0) = '2021-10-14 02:00:00', @End datetime2(0) = '2021-10-16 10:30:00';
SELECT PeriodStart,
       CASE WHEN DATEADD(DAY, 1, PeriodStart) < @End THEN DATEADD(DAY, 1, PeriodStart) ELSE @End END AS PeriodEnd
FROM (SELECT DATEADD(DAY, value, @Start) AS PeriodStart FROM GENERATE_SERIES(0, DATEDIFF(DAY, @Start, @End))) AS p
WHERE PeriodStart < @End;

The last row is the short period of 8 hours and 30 minutes. If the end were 2021-10-16 at 02:00, the result would hold the first two rows only.

Fill the Gaps in a Report

A date range earns its keep in reports. A sales query lists only days that had sales. A LEFT JOIN from the date range to the data lists every day and shows zero where nothing sold. The demo uses 2024, so the range includes the leap day.

DECLARE @StartDate date = '2024-02-27', @EndDate date = '2024-03-02';
DECLARE @Sales TABLE (SaleDate date, Amount decimal(9,2));
INSERT @Sales VALUES ('2024-02-27', 40), ('2024-02-27', 10), ('2024-02-29', 25), ('2024-03-02', 5);
SELECT d.Day, COALESCE(SUM(s.Amount), 0) AS Sales
FROM (SELECT DATEADD(DAY, value, @StartDate) AS Day FROM GENERATE_SERIES(0, DATEDIFF(DAY, @StartDate, @EndDate))) AS d
LEFT JOIN @Sales AS s ON s.SaleDate = d.Day
GROUP BY d.Day
ORDER BY d.Day;

You could argue that the recursive version is easier to read, because the anchor and the step are right there. That is fair for a short range. For a year or a decade, GENERATE_SERIES or the number list is shorter. It is also free of the recursion limit.

What to Remember

To generate a date range, pick the method by version. Use GENERATE_SERIES on SQL Server 2022 and later, with compatibility level 160. Use a recursive CTE with an explicit MAXRECURSION value on older servers, or the cross join of system objects. Leave out the end dates by starting at 1 and stopping one day early.

If many reports need the same dates every day, store them once in a calendar table and index it. The scripts above build the list when you do not want to keep a table. They answer the question for any range in one statement.

A date range is not a table you have to store, it is a list you can build on demand.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.