T-SQL: Dynamically Create Date Ranges With Recursive Queries

In this post I will provide a method how you can dynamically create a range of values.
I recently had to get a range of month values for filtering on a data set, but I had no date table on the database and needed to find a quick way how to solve this issue without going the route of creating a table and loading values.
So I ended up writing a recursive query using a Common Table Expression.

DECLARE @MinDate date = '2015-01-01',
@EndDate date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
     
;WITH DateSerial AS
(
    SELECT @MinDate MonthDate  -- 1 Invocation of the routine

    UNION ALL
 
    SELECT DATEADD(MONTH, 1, MonthDate)   --Recursive invocation of the routine
    FROM DateSerial
    WHERE MonthDate < @EndDate)

SELECT * FROM DateSerial

OPTION (MAXRECURSION 0)

In the first part I declared a start and end date to define the range.

A recursive Common Table Expression can be broken down into 3 parts:

1) Invocation of the routine
2) Recursive invocation of the routine
3) Termination Check

In the above sample a Common Table Expression is used where a first Select picks up the start date
and a UNION ALL combines the other results that are created until the end date is reached.

Depending on how big the range is, you are very likely to receive this error message at some point:

"The maximum recursion .... has been exhausted before statement completion"

To avoid this error message, the command OPTION (maxrecursion n) lets you define how often the Common Table Expression can recurse until it reaches an error state. In my sample I set the value to 0, meaning infinite recursion.


The querey delivered follwing result: