Get last 6 months monthname.month number and Years in simple select statement

Question

How to get last 6 months month name, month number and Years in simple select statement in sqlserver . The no of months is 6, and is fixed

12  Dec 2015
11  Nov 2015
10  Oct 2015
9   Sep 2015
8   Aug 2015
7   Jul 2015
6   Jun 2015 

This should handle year end boundaries

say, if the current month is Feb 2016, the result should give 2015 months.

2   Feb 2016
1   Jan 2016
12  Dec 2015
11  Nov 2015
10  Oct 2015
9   Sep 2015
8   Aug 2015

Answer 1

You can do it with the following:

SELECT  MONTH(DATEADD(mm, -m, GETDATE())) AS m,
        LEFT(DATENAME(mm,  DATEADD(mm, -m, GETDATE())), 3) AS n,
        YEAR(DATEADD(mm, -m, GETDATE())) AS y
FROM    (VALUES (0),(1),(2),(3),(4),(5),(6)) t(m) 

Output:

m   n   y
12  Dec 2015
11  Nov 2015
10  Oct 2015
9   Sep 2015
8   Aug 2015
7   Jul 2015
6   Jun 2015

Answer 2

Try this

;with cte as
(
select 0 as num
union all
select num+1 from cte where num<6
)
select month(dates),datename(month,dates),year(dates)
from 
(
select dateadd(mm,-num,datadd(dd,1,eomonth(getdate(),-1))) as dates
from cte
) A

SQL FIDDLE DEMO