Get the month end dates of last N months

Here is a simple T-SQL script that may come handy if you need the month end dates of last N months. N is the number of months.

/*
	Assign the dynamic number of months here. 
	You can write a procedure / table-valued function to accept this value as a parameter. 
*/
DECLARE @Last_N_Months		INT		=	24

; WITH cte_last_N_months
AS
(
	SELECT DATEADD(MM, (@Last_N_Months * -1), GETDATE()) AS [Date]
	UNION ALL
	SELECT DATEADD(MM, 1, [Date]) AS [Date]
	FROM cte_last_N_months
	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_months
ORDER BY 1 ASC
OPTION (MAXRECURSION 0);

The output will look like as can be seen in the image below.

2 thoughts on “Get the month end dates of last N months

  1. No need for recursion.
    You can work set based:

    DECLARE @Last_N_Months tinyint = 24
    , @Include_Current_Month bit = 1;

    ;with Differ(Increment) as(
    select top(@Last_N_Months) ROW_NUMBER() over (order by @@spid) – @Include_Current_Month
    from master..spt_values
    )
    select EOMONTH(dateadd(MM, -1 * Increment, current_timestamp)) as MonthEnd
    , @Last_N_Months – Increment + 1 – @Include_Current_Month as MonthID
    from Differ;

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s