Generating dates Iteratively

First of all we declare 2 variables defining the start date where to start from second is numbe of months for which we want to loop.

We use ;with iteratation to acheive this tasks that selects each date and than add a day into it till its condition is true.

Folowing is the code snippet that generates all days in a month month.


DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '20080101', @NumberOfMonths = 1
;WITH Days AS (
SELECT @FirstDay as CalendarDay
UNION ALL
SELECT DATEADD(d, 1, CalendarDay) as CalendarDay
FROM Days
WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)
)
SELECT * FROM Days

Folowing is the code snippet that generates next 12 months from the current date.


DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '20080101', @NumberOfMonths = 12
;WITH Days AS (
SELECT @FirstDay as CalendarDay
UNION ALL
SELECT DATEADD(m, 1, CalendarDay) as CalendarDay
FROM Days
WHERE DATEADD(m, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)
)
SELECT CONVERT(char(3), CalendarDay, 100) AS monthName,DATEPART(mm,CalendarDay) AS MonthNo FROM Days

Further, user can play with it with combination with other table to acheive desired results.