Here is a simple T-SQL script that may come handy if you need the month end dates of last N years. N is the number of years.
/* Assign the dynamic number of years here. You can write a procedure / table-valued function to accept this value as a parameter. */ DECLARE @Last_N_Years INT = 2 ; WITH cte_last_N_years AS ( SELECT DATEADD(YY, (@Last_N_Years * -1), GETDATE()) AS [Date] UNION ALL SELECT DATEADD(MM, 1, [Date]) AS [Date] FROM cte_last_N_years WHERE [Date] < DATEADD(MM, -1, CAST(GETDATE() AS DATE)) ) SELECT EOMONTH([Date]) AS [Date] , ROW_NUMBER() OVER(ORDER BY [Date] ASC) AS MonthID FROM cte_last_N_years ORDER BY 1 ASC OPTION (MAXRECURSION 0);
The output will look like as can be seen in the image below.
6 thoughts on “Get the month end dates of last N years”
Very nice! Here’s another way to do it without using recursion:
DECLARE @Last_N_Years int = 2;
WITH c1 AS (SELECT x.x FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) x(x))
, c2(x) AS (SELECT 1 FROM c1 x CROSS JOIN c1 y)
, c3(x) AS (SELECT 1 FROM c2 x CROSS JOIN c2 y CROSS JOIN c2 z)
, c4(rn) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM c3)
SELECT EOMONTH(DATEADD(MONTH, -x.rn,GETDATE()))
FROM c4 x
ORDER BY x.rn DESC;
This is using a method I learned from Jeff Moden (not sure where he learned it) to generate a tally table.
LikeLiked by 1 person
Weird, the WHERE clause got stripped a bit. Must have gotten cleaned out to remove brackets.
I put it here instead:
LikeLiked by 1 person
I used a simple loop, without any tally table and got the same. Compared with CTE, the cost is 37% vs 63% with CTE. And the end date can be changed.
declare @year int = 2
declare @endDate date = getdate()
declare @startDate date = dateadd(month, -@year * 12, @endDate)
declare @month table (EOM date)
declare @current int = 0
while (@current < @year * 12)
insert into @month
values(EOMONTH(dateadd(month, @current, @startDate)))
set @current += 1
select * from @month
Using either a Recursive CTE or a While loop is resource intensive method even for supposed “small” rowcounts. The only way to break the habit is to stop practicing the habit just because you have a small row count.