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.

2 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)
    SELECT EOMONTH(DATEADD(MONTH, -x.rn,GETDATE()))
    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

    1. Weird, the WHERE clause got stripped a bit. Must have gotten cleaned out to remove brackets.

      I put it here instead:

      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
      WHERE x.rn <= 12 * @Last_N_Years
      ORDER BY x.rn DESC;

      view raw
      gistfile1.sql
      hosted with ❤ by GitHub

      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