DECLARE @FirstDay smalldatetime, @NumberOfMonths INT

SELECT @FirstDay = '20080201', @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 CONVERT(CHAR(8), CalendarDay, 112)
FROM Days

Credits:

Mikhail Berlyant
Senior Data Architect
MySpace.com

Posted at eggheadcafe.com