EN VI

Sql-server - How do I list the months for the last 10 years starting from the current month in SQL Server?

Sql-server - How do I list the months for the last 10 years starting from the current month in SQL Server

How do I list the months for the last 10 years starting from the current month using SELECT statement?

I can list all the current months since the last 10 years but could not list all the 12 subsequent months in the years

select top 10 dateadd(year, -1*row_number() over (order by name)+1, convert (varchar, getdate(),112)) as y
from sys.objects

will give me the last 10 years from the current month

202403...201503

select top 12 dateadd(MM, -1*row_number() over (order by name)+1, convert (varchar, getdate(),112)) as m
from sys.objects

will give me the last 12 month from today

But how do I merge the 2 statements above to get all the months from the last 10 years from the current month?

Please advise.

Solution:

Just one option using a ad-hoc tally/numbers table

Select N
      ,YrMth = format(dateadd(month,-N,getdate()),'yyyyMM')
 From  (
        Select Top (121) N=-1+Row_Number() Over (Order By (Select NULL)) 
         From  sys.objects
      ) B

Results

Tags

Pinned Posts

Top 5 Best High CPM Ads Networks For Publishers In 2023

Tags: ads, Publishers, Ads Networks

HawkHost Coupon 2023 – Hawk Hosting Discount Code Up to 40% Lifetime Update Every Day

Tags: Server, host, cloud

Author

Best Solution

Bot Lv 9

Keyword

How do I list the months for the last 10 years starting from the current month in SQL Server, sql-server

Answer