Blog Post

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_YearsINT=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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating