Recursive CTE to Calculate Dates for the Last 30 Days

Have you ever needed to generate a list of dates for the last 30 days. There are lots of different ways to do this with TSQL, but one way is with a CTE, where you can then left join against it with another query to generate a list of all dates in a result set, along with the dates for the last 30 days that aren’t in the result set.

DECLARE @today as date;
DECLARE @30ago as date;
select @today = DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate())),
	   @30ago = DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate() - 30));
;WITH Last30DaysCTE as (
   SELECT @30Ago as CalendarDate
   UNION ALL
   SELECT dateadd(day , 1, CalendarDate) AS CalendarDate FROM Last30DaysCTE
   WHERE dateadd (day, 1, CalendarDate) < @today
)
SELECT *
  FROM Last30DaysCTE
OPTION (MAXRECURSION 30);

Which creates this list.

Just a quick CTE that can be very useful when generating a report.

Enjoy…

Steve Stedman

-Bellingham WA

1 thought on “Recursive CTE to Calculate Dates for the Last 30 Days”

Kleidi April 22, 2019 at 08:31


Thanks.
Exccellent. Works flawlessly.

Could have been easier to understand if the variable names would have been named @StartPeriod and another one would have been @NumOfDays. That way this could have been more generic and easier to suit more needs for forward and backwards dates.

Thanks again

Recursive CTE to Calculate Dates for the Last 30 Days

1 thought on “Recursive CTE to Calculate Dates for the Last 30 Days”

Leave a Reply Cancel reply