Generate date range (Year and Month)

Following  T-SQL query can be used to generate Month Names and Year Names within a given period of time.

DECLARE
    @StartDate DATETIME,
    @EndDate   DATETIME;
--Define start date and end date
SELECT @StartDate = '2013-04-01', @EndDate = '2014-03-01';
WITH cteDates AS (
    SELECT TOP (
        DATEDIFF (mm, @StartDate, @EndDate) + 1)
        DATEADD( mm, DATEDIFF(mm,0, @StartDate ) + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0
     ) AS MonthDate
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT
    YEAR(MonthDate) AS [Year],
    MONTH(MonthDate) AS [Month]
FROM cteDates

Ref:http://stackoverflow.com/questions/4181286/t-sql-select-get-all-months-within-a-range-of-years