How to select last 12 months name and year without using tables using SQL query?

Question

how can i select the last 12 month name using current time. without using any table, if the current month is October, then I want to get the result as

month year oct 2011 nov 2011 dec 2011 jan 2012 feb 2012 mar 2012 apr 2012 may 2012 jun 2012 jul 2012 aug 2012 sep 2012 oct 2012 

I do not have a table in the database for this.

Answer 1

 SET LANGUAGE English; WITH R(N) AS ( SELECT 0 UNION ALL SELECT N+1 FROM R WHERE N < 12 ) SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month], DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS [year] FROM R 

Answer 2

 declare @start DATE = '2011-10-01'; with CTEE(date) AS ( SELECT @start UNION all SELECT DATEADD(month,-1,date) from CTEE where DATEADD(month,-1,date)>=DATEADD(month,-10,@start) ) select Datename(month,date) from CTEE 

Answer 3

 SELECT STR(MONTH(DATEADD(mm, number, GETDATE())), 2) AS MonthNum, DATENAME(month, DATEADD(month, MONTH(DATEADD(mm, number, GETDATE())), 0) - 1) AS MonthNames FROM master.dbo.spt_values WHERE (name IS NULL) AND (number BETWEEN 0 AND 11) 

Answer 4

Use MVJ F_TABLE_DATE: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519