r.mitchell (6/26/2013)

---

I use a recursive CTE for this usually. In this example it will give you all the days in June. Had to use this when I created a calendar application with recurring entries. Sort of like outlook and needed to match the day of week based on its recurrence schedule. Like every week, 2 weeks, month... etc.

DECLARE @StartDate DATE = '6/1/2013';

WITH dt(MDate) AS (

SELECT @StartDate AS MDate

UNION ALL

SELECT DATEADD(dd, 1, MDate)

FROM dt

WHERE DATEADD(dd, 1, MDate) < DATEADD(mm, 1, @StartDate)

)

SELECT MDate FROM dt