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