Recursive CTE in SQL SERVER

This shows the use of recursive cte that displays a next six month based on passing the values (Year and Month).


DECLARE @year int,
@month int

SELECT @year = 2010,
@month = 9

;WITH cte AS (
SELECT
DATEADD(MONTH,@month-1,DATEADD(year,@year-1,cast('00010101' as date))) as date1
UNION ALL
SELECT DATEADD(month,1,date1)
FROM cte
WHERE date1 < DATEADD(MONTH,@month + 4,DATEADD(year,@year-1,cast('00010101' as date)))
)

SELECT * FROM cte
GO

And Output will be :

date1
-------------
2010-09-01
2010-10-01
2010-11-01
2010-12-01
2011-01-01
2011-02-01