Easiest way to populate a temp table with dates between and including 2 date parameters

Question

What is the easiest way to populate a temp table with dates including and between 2 date parameters. I only need the 1st day of the month dates.

So for example if @StartDate = '2011-01-01' and @EndDate = '2011-08-01'

Then I want this returned in the table

2011-01-01 2011-02-01 2011-03-01 2011-04-01 2011-05-01 2011-06-01 2011-07-01 2011-08-01 

Answer 1

This works even if the @StartDate is not the first of the month. I'm assuming that if it's not the start of the month, you want to begin with the first of the next month. Otherwise remove the +1.:

;WITH cte AS ( SELECT CASE WHEN DATEPART(Day,@StartDate) = 1 THEN @StartDate              ELSE DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0) END AS myDate UNION ALL SELECT DATEADD(Month,1,myDate) FROM cte WHERE DATEADD(Month,1,myDate) <=  @EndDate ) SELECT myDate FROM cte OPTION (MAXRECURSION 0)