DECLARE @StartDate DATETIME = '2014-01-10' DECLARE @EndDate DATETIME = '2014-12-11'; WITH Dates AS ( SELECT DATEADD(DAY, -(DAY(@StartDate) - 1), @StartDate) AS [Date] UNION ALL SELECT DATEADD(MONTH, 1, [Date]) FROM Dates WHERE [Date] < DATEADD(DAY, -(DAY(@EndDate) - 1), @EndDate) ) SELECT YEAR([Date]) AS [Year], MONTH([Date]) AS [Month], DATENAME(MM, [Date]) AS [MonthName] FROM Dates;