One puzzle SQL developers face from time to time is creating a calendar containing a series of days.

Below is some SQL code that will create an in-memory calendar for every day from a begin date to an end date.

By adding more columns to the #Calendar table you could store more information about each day, store hours and pay rate to do pay calculations, or other data you need to capture and process in a series of days.

The example creates a calendar for 2009.  Happy New Year!

Example

DROP TABLE #Calendar

CREATE TABLE #Calendar

(

CalendarDate DATETIME

)

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '1/1/2009'

SET @EndDate = '12/31/2009'

WHILE @StartDate <= @EndDate

      BEGIN

             INSERT INTO #Calendar

             (

                   CalendarDate

             )

             SELECT

                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)

      END

-- See the results

SELECT * FROM #Calendar