Get a Table of Months Between Two Dates

Sometimes you need a list of months between two dates. Admittedly, this is another case where a calendar table would come in VERY handy. Unfortunately, I have not been able to build one yet. In case you’re in a similar situation, here’s how you could go about doing this:``` CREATE FUNCTION dbo.GetMonthList ( @StartDate DATETIME, @EndDate DATETIME ) RETURNS @months TABLE ( [month] DATETIME ) WITH EXECUTE AS OWNER AS BEGIN /******************************************************************************

• Author: Jeremiah Peschka
• Create Date: 2008-11-20
• Description: Create a table of dates between @StartDate and @EndDate *****************************************************************************/ DECLARE @MonthDiff INT; DECLARE @counter INT; DECLARE @tbl TABLE ([month] DATETIME);

SET @StartDate = ‘2008-01-01’; SET @EndDate = ‘2008-12-01’;

SET @counter = 0;

SELECT @MonthDiff = DATEDIFF(mm, @StartDate, @EndDate);

WHILE @counter <= @MonthDiff BEGIN INSERT @months SELECT (DATEADD(mm, @counter, @StartDate));

SET @counter = @counter + 1;

END

RETURN; END GO