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.

4 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

  2. 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
    values(EOMONTH(dateadd(month, @current, @startDate)))
    set @current += 1
    end
    select * from @month

    Like

  3. 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.

    Like

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 )

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