dynamically get last x months and output as a format Using today's

date, I need to be able to go back x months and output in the format of YYYY_MM

At the moment, I am using:

select 
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ '_'
+ right('00' + cast(datepart(month, DATEADD(month, -3, GETDATE())) as varchar(2)), 2)


Which will give me a output of: 


  2015_08


The issue I am having is that if I were to go back 12 months using: 

select 
right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4)
+ '_'
+ right('00' + cast(datepart(month, DATEADD(month, -12, GETDATE())) as varchar(2)), 2)


The output would still be:


  2015_11 (notice the year is still the same)


I am using SQL 2008

EDIT: Solution

DECLARE @table12 AS varchar(50)
SET @table12 = 'SMD_' + replace(convert(varchar(7),DATEADD(MONTH, -12, GETDATE()),102), '.', '_')
I hope that you are not repeating that expression for dateadd(month, -4, dateadd(month, -5, etc. Here is a much tidier way to develop an aggregate query that covers the past 12 months:

DECLARE @start DATE = DATEADD(DAY,1-DAY(GETDATE()),GETDATE())
;WITH Last12Months AS
(
  SELECT TOP (12) m = DATEADD(MONTH, -(ROW_NUMBER() OVER 
    (ORDER BY [object_id])-1), @start)
  FROM sys.all_objects
  ORDER BY [object_id]
)
SELECT m.m, COUNT(o.[key])
FROM Last12Months AS m
LEFT OUTER JOIN dbo.OtherTable AS o
  ON o.DateTimeColumn &gt;= m.m
  AND o.DateTimeColumn &lt; DATEADD(MONTH, 1, m.m)
GROUP BY m.m
ORDER BY m.m;


Now, this will output the date as:

2014-12-01


If you really need 2014_12, then either (a) format it in the presentation tier (best practice!) or (b) do something really inefficient and format it in T-SQL. So the SELECT part of the query above becomes:

SELECT m = REPLACE(CONVERT(CHAR(7), m, 120), '-', '_'), c
FROM 
(
  SELECT m.m, c = COUNT(o.[key])
  FROM Last12Months AS m
  LEFT OUTER JOIN dbo.OtherTable AS o
    ON o.DateTimeColumn &gt;= m.m
    AND o.DateTimeColumn &lt; DATEADD(MONTH, 1, m.m)
  GROUP BY m.m
) AS wasted_cpu_cycles
ORDER BY m;