How to Get All Months between two dates in SQL Server?

Updated on September 28, 2015     Kisan Patel

Problem:

Is it possible to get all month names between two dates in SQL Server?

Solution:

You can do this with a recursive CTE, by building up a table of dates, and getting the month name from each:

declare @start DATE = '2015-01-01'
declare @end DATE = '2015-12-31'

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date) <= @end
)
select Datename(month,date) from months

Output of the above sql query will be—