Get list of dates using recursive Common Table Expression (CTE)

Many times we need a list of consecutive dates or time at runtime mainly for a recursive ETL process. This code provides a list almost instantaneously with only the start and end dates being the only input. The output is in Date format but is easily extended to a DateTime format.

For best results, you can use this in a CTE as part of the solution so that it does not take up space in the database.

The MaxRecursion option ensures that the recursion does not go on forever, however the Per Minute and Per Second versions will definitely overrun the MaxRecursion option.

Note: The code for each one are similar, the only difference is the first parameter of the DATEADD function which determines the recursive detail.

Daily
Monthly
Yearly
Hourly
Per Minute
Per Second

Daily:

DECLARE @StartDate DateTime = '20130101'
DECLARE @EndDate DateTime = GETDATE();

WITH DateRange(DateTime) AS
(
SELECT
@StartDate DateTime
UNION ALL
SELECT
DATEADD(day, 1, DateTime) Date
FROM
DateRange
WHERE
DateTime < @EndDate
)
SELECT DateTime
FROM DateRange
–You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
OPTION (MaxRecursion 10000)

Monthly:

DECLARE @StartDate DateTime = '20130101'
DECLARE @EndDate DateTime = GETDATE();

WITH DateRange(DateTime) AS
(
SELECT
@StartDate DateTime
UNION ALL
SELECT
DATEADD(month, 1, DateTime) Date
FROM
DateRange
WHERE
DateTime < @EndDate
)
SELECT DateTime
FROM DateRange
–You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
OPTION (MaxRecursion 10000)

Yearly:

DECLARE @StartDate DateTime = '20130101'
DECLARE @EndDate DateTime = GETDATE();

WITH DateRange(DateTime) AS
(
SELECT
@StartDate DateTime
UNION ALL
SELECT
DATEADD(year, 1, DateTime) Date
FROM
DateRange
WHERE
DateTime < @EndDate
)
SELECT DateTime
FROM DateRange
–You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
OPTION (MaxRecursion 10000)

Hourly:

DECLARE @StartDate DateTime = '20130101'
DECLARE @EndDate DateTime = GETDATE();

WITH DateRange(DateTime) AS
(
SELECT
@StartDate DateTime
UNION ALL
SELECT
DATEADD(hh, 1, DateTime) Date
FROM
DateRange
WHERE
DateTime < @EndDate
)
SELECT DateTime
FROM DateRange
–You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
OPTION (MaxRecursion 10000)

Per minute:

DECLARE @StartDate DateTime = '20130101'
DECLARE @EndDate DateTime = GETDATE();

WITH DateRange(DateTime) AS
(
SELECT
@StartDate DateTime
UNION ALL
SELECT
DATEADD(minute, 1, DateTime) Date
FROM
DateRange
WHERE
DateTime < @EndDate
)
SELECT DateTime
FROM DateRange
–You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
OPTION (MaxRecursion 10000)

Per second:

DECLARE @StartDate DateTime = '20130101'
DECLARE @EndDate DateTime = GETDATE();

WITH DateRange(DateTime) AS
(
SELECT
@StartDate DateTime
UNION ALL
SELECT
DATEADD(second, 1, DateTime) Date
FROM
DateRange
WHERE
DateTime < @EndDate
)
SELECT DateTime
FROM DateRange
–You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
OPTION (MaxRecursion 10000)

One thought on “Get list of dates using recursive Common Table Expression (CTE)

Leave a Reply

Your email address will not be published.