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

Question

I need to make a temporary table that holds of range of dates, as well as a couple of columns that hold placeholder values (0) for future use. The dates I need are the first day of each month between $startDate and $endDate where these variables can be several years apart.

My original sql statement looked like this:

select dbo.FirstOfMonth(InsertDate) as Month, 0 as Trials, 0 as Sales
into #dates
from customer
group by dbo.FirstOfMonth(InsertDate)

"FirstOfMonth" is a user-defined function I made that pretty much does what it says, returning the first day of the month for the provided date with the time at exactly midnight.

This produced almost exactly what I needed until I discovered there were occasionally gaps in my dates where I had a few months were there were no records insert dates. Since my result must still have the missing months I need a different approach.

I have added the following declarations to the stored procedure anticipating their need for the range of the dates I need ...

declare $startDate set $startDate = select min(InsertDate) from customer
declare $endDate set $endDate = select max(InsertDate) from customer

... but I have no idea what to do from here.

I know this question is similar to this question but, quite frankly, that answer is over my head (I don't often work with SQL and when I do it tends to be on older versions of SQL Server) and there are a few minor differences that are throwing me off.

Answer 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