# Get the month end dates of last N years

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”

1. 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)
FROM c4 x
WHERE x.rn
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.

Liked by 1 person

2. John Smith says:

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)
begin
insert into @month