SQL – Calculating Dates Between a Range

How to calculate dates between a date range using a recursive CTE.

Issue

I need to list the dates a price table was missing values for. The range of dates I need to check are in the trades table. Therefore, all I needed to do is to generate all the dates in the range then left join to the price table to determine which dates didn’t return a value; but how do I create a list of dates between a range without nesting while loops?

Solution

A recursive CTE provides a compact way to calculate the dates. To keep this example very simple, only the date calculation is presented.

Notice there are two selects within the CTE; the first is the base case and selects the StartDate and EndDate. This is also where I joined to the trade table to get the StartDate, EndDate and TradeID.

The second select is the recursive case which recursively calls HierarchyCTE via the JOIN to add one day to NextDate until NextDate equals EndDate.

The select after the CTE is where I left joined to the price table using the TradeID to determine the dates missing for each TradeID.

By default the maximum number of recursion levels allowed is 100. You can override this with the MaxRecursion option.

DECLARE @StartDate DATE = '1/26/2025'
       ,@EndDate DATE = '2/4/2025'

;WITH HierarchyCTE AS (
    SELECT -- Base select
         [StartDate] = @StartDate
        ,[EndDate] = @EndDate
        ,[NextDate] = @StartDate

UNION ALL

    SELECT -- Recursive select
        cte.StartDate
       ,cte.EndDate
       ,[NextDate] = DATEADD(DAY, 1, cte.NextDate)
    FROM HierarchyCTE cte
    WHERE NextDate < EndDate
)
SELECT *
FROM HierarchyCTE
OPTION(MAXRECURSION 0) -- Disable maximum recursion.  Default is 100 levels.

Output: Dates Calculated

Conclusion

Hierarchical CTE allows for calculating the dates between a range much cleaner than a while loop. In this example, there is only one row from the base select. If you were to join to other tables such as trade table as I did, then there would be a row for each tradeID and the recursive case would process each of those. I found the recursions fast with millions of rows produced within a few seconds.