Using a recursive common table expression to generate a date range

At one company that I worked for, I inherited a bunch of reporting code written in T-SQL. A lot of it was for reporting on monthly information. Sometimes where were inserts into temp tables for month data and the performance was terrible.

One consultant I hired saw some of the T-SQL code and suggested using a recursive common table expression (CTE) to generate dates. This was much faster than inserting into a temp table.

Here is an example of a CTE to generate the range of days for the current month. Obviously using a recursive algorithm can be dangerous. There is a limit on the number of recursive calls that can be made. You can add OPTION (MAXRECURSION N); to the select from a recursive CTE to limit the number of interations where N is a number between 0 and 32,767.

 
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = DATEADD(month, DATEDIFF(month,'20120101', CURRENT_TIMESTAMP), '20120101')
SET @EndDate = DATEADD(month, DATEDIFF(month,'20120131', CURRENT_TIMESTAMP), '20120131')

;WITH DateRange(Date) AS
    (
        SELECT
            @StartDate Date
        UNION ALL
        SELECT
            DATEADD(day, 1, Date) Date
        FROM
            DateRange
        WHERE
            Date < @EndDate
    )
SELECT * FROM DateRange