Jeremiah Peschka

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