Every now and then, I need to list all the days for a given date range. A long time ago, I blogged about a UDF to do it (http://emoreau.com/Entries/Blogs/2009/01/SQL-A-UDF-User-defined-function-returning-all-days-between-2-dates.aspx).

I have seen a trick a couple of months ago that I haven’t had the time to test and which I revisited this morning.

This newer way is using a CTE (Common Table Expression).

The query looks like this:

DECLARE @StartDate DATE = '2016-04-01'
DECLARE @EndDate DATE = GETDATE()
;
WITH cteDates AS
(
 SELECT @StartDate AS DateValue
 UNION ALL
 SELECT DATEADD(DAY, 1, DateValue)
 FROM cteDates
 WHERE DATEADD(DAY, 1, DateValue) <= @EndDate
) 
SELECT DateValue, DATEPART(WEEKDAY, DateValue) AS DayNumber, DATENAME(WEEKDAY, DateValue ) AS Weekday 
FROM cteDates

That will return the expected results in no time. But depending on your range, you may get this error:

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

By default, the CTE sets a limit to the number of recursion to 100. That limit is set to protect you from eating all the resources from your database server.

But in this specific case, we know that the recursion is not really hurting. We can change that limit for that query only using the “Option MaxRecursion” clause. If for example, we know that we won’t return much more than a year worth of days, we might want to set the limit to 400. The new query would look like this:

SELECT DateValue, DATEPART(WEEKDAY, DateValue) AS DayNumber, DATENAME(WEEKDAY, DateValue ) AS Weekday 
FROM cteDates
OPTION (MAXRECURSION 400)

We could also set the limit to any values between 0 and 32,767. If you set it to 0, you might cause an infinite loop so it is at your own risk.