Recursive CTE to Calculate Dates for the Last 30 Days

Recursive CTE to Calculate Dates for the Last 30 Days
Download PDF

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…


Common table expressions

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

Download your free copy today!


 

More from Stedman Solutions:

SteveStedman5
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!

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

  1. 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 “&lt;” 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.

  2. 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 :)

Leave a Reply

Your email address will not be published. Required fields are marked *

*