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 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) < @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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
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.
Thanks Dave for finding that error. It is fixed now.
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 :)