Recursive CTE to Calculate Dates for the Last 30 Days

by SteveStedman
September 1, 2021March 9, 2022
3 Comments
CTE

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 recursive CTE. 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) &amp;lt; @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…

Receive a FREE copy of my Common Table Expressions book when you sign up for my mailing list.

Download your free copy today!

3 thoughts on “Recursive CTE to Calculate Dates for the Last 30 Days”

Dave Bundus September 7, 2021 at 06:52

Reply

Thanks for this great CTE example. I can see myself using this as a template for other projects.
I think something might not have formatted correctly when creating this blog post.
I believe “<” on line 9 is supposed to be a less than symbol. “<"
But maybe its just and issue with how my browser is displaying the code content.
1. Stedman Solutions September 7, 2021 at 11:30
  
  Reply
  
  Thanks Dave for finding that error. It is fixed now.
Mithat September 7, 2021 at 11:59

Reply

Hi, instead of OPTION (MAXRECURSION 30), I prefer adding TOP 30 to the query.
If you would like to limit the values using MAXRECURSION, you’ll get The maximum recursion n has been exhausted before statement completion. TOP will limit it without any issue :)