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

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!