Populating a "Date" dimention Table

[aswolff]

I need to create a table with rows for everyday of the year for the next 3 years. Example columns is shown below:

T_Date : 03/01/2008
DayofYear: 61
WeekOfYear: 09
Quarter: 02
DayofWeek: Sat

This is for a time dimension for a DataWarehouse. Can anybody point me to a sample function, stored_procedure, sql or anything that shows how I might accomplish this?

Thank you in advance.

 

[pbaldy]

Here's a portion of an SP that starts by building a temp table with dates. Does this get you started?

Set @CurDate = @StartDate
--Build temp table with dates for current month
SELECT @CurDate as DateField
INTO #tblDates

Set @CurDate = DateAdd(day,1,@CurDate)

While @CurDate <= @EndDate
Begin
INSERT INTO #tblDates(DateField)
VALUES(@CurDate)

Set @CurDate = DateAdd(day, 1, @CurDate)
End

Paul
MS Access MVP 2007/2008

 

[aswolff]

Yes. Thanks.