How can I generate a temporary table filled with dates in SQL Server 2000?

Solution 1:

I needed something similar, but all DAYS instead of all MONTHS.

Using the code from MatBailie as a starting point, here's the SQL for creating a permanent table with all dates from 2000-01-01 to 2099-12-31:

CREATE TABLE _Dates (
  d DATE,
  PRIMARY KEY (d)
)
DECLARE @dIncr DATE = '2000-01-01'
DECLARE @dEnd DATE = '2100-01-01'

WHILE ( @dIncr < @dEnd )
BEGIN
  INSERT INTO _Dates (d) VALUES( @dIncr )
  SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END

Solution 2:

This will quickly populate a table with 170 years worth of dates.

CREATE TABLE CalendarMonths (
  date DATETIME,
  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = '01 Jan 2000',
  @offset = 1

WHILE (@offset < 2048)
BEGIN
  INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
  SELECT @offset = @offset + @offset
END

You can then use it by LEFT joining on to that table, for the range of dates you require.

Solution 3:

I would probably use a Calendar table. Create a permanent table in your database and fill it with all of the dates. Even if you covered a 100 year range, the table would still only have ~36,525 rows in it.

CREATE TABLE dbo.Calendar (
    calendar_date    DATETIME    NOT NULL,
    is_weekend       BIT         NOT NULL,
    is_holiday       BIT         NOT NULL,
    CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)

Once the table is created, just populate it once in a loop, so that it's always out there and available to you.

Your query then could be something like this:

SELECT
    C.calendar_date,
    0 AS trials,
    0 AS sales
FROM
    dbo.Calendar C
WHERE
    C.calendar_date BETWEEN @start_date AND @end_date AND
    DAY(C.calendar_date) = 1

You can join in the Customers table however you need to, outer joining on FirstOfMonth(InsertDate) = C.calendar_date if that's what you want.

You can also include a column for day_of_month if you want which would avoid the overhead of calling the DAY() function, but that's fairly trivial, so it probably doesn't matter one way or another.

Solution 4:

This of course will not work in SQL-Server 2000 but in a modern database where you don't want to create a permanent table. You can use a table variable instead creating a table so you can left join the data try this. Change the DAY to HOUR etc to change the increment type.

declare @CalendarMonths table (date DATETIME,  PRIMARY KEY (date)
)

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = '01 Jan 2014',
  @offset = 1
  INSERT INTO @CalendarMonths SELECT @basedate

WHILE ( DATEADD(DAY, @offset, @basedate) < CURRENT_TIMESTAMP)
BEGIN
  INSERT INTO @CalendarMonths SELECT DATEADD(HOUR, @offset, date) FROM @CalendarMonths where DATEADD(DAY, @offset, date) < CURRENT_TIMESTAMP
  SELECT @offset = @offset + @offset
END