There are tons of script avaliable out there, you can go to Google and search for populate datetime dimension, here is one example:
set nocount on
go
drop function dbo.date$calcIdValue
go
create function dbo.date$calcIdValue
(
@dateValue datetime
)
returns int
as
begin
return datediff(day,'1/1/1800',@dateValue)
end
go
drop table generic_date_dim
go
CREATE TABLE generic_date_dim (
generic_date_dim_id int not null primary key,
date_value smalldatetime NOT NULL unique,
the_day varchar(60) NOT NULL,
the_month varchar(60) NOT NULL,
the_year varchar(60) NOT NULL,
day_of_the_month int NOT NULL,
day_of_the_year int NOT NULL,
week_of_the_year int NOT NULL,
month_of_the_year int NOT NULL,
calendar_quarter int NOT NULL,
fiscal_year int NOT NULL,
fiscal_quarter int NOT NULL
)
go
declare @startDay datetime, @endDay datetime
select @startDay = '1/1/2003',
@endDay = '4/30/2004'
declare @i int, @currentDay smalldatetime
set @currentDay = @startDay
begin transaction
while @currentDay <= @endDay
begin
insert into generic_date_dim([generic_date_dim_id], [date_value],
[the_day], [the_month], [the_year], [day_of_the_month], [day_of_the_year],
[week_of_the_year], [month_of_the_year], [calendar_quarter], [fiscal_year],
[fiscal_quarter])
select dbo.date$calcIdValue(@currentDay),
@currentDay as dateValue,
datename(dw, @currentDay) as theDay,
datename(month, @currentDay) as theMonth,
datename(year, @currentDay) as theYear,
datepart(day,@currentDay) as dayOfTheMonth,
datepart(dayofyear,@currentDay) as dayOfTheYear,
datepart(week,@currentDay) as weekOfTheYear,
datepart(month,@currentDay) as monthOfTheYear,
cast((datepart(month,@currentDay) / 4) + 1 as varchar(3)) as
calendarQuarter,
datepart(year, @currentDay) as fiscalYear,
cast((datepart(month,@currentDay) / 4) + 1 as varchar(3)) as fiscalQuarter
if @@error <> 0
begin
raiserror 50000 'Error creating timeByDay dimension'
rollback transaction
goto endofscript
end
set @currentDay = dateadd(day,1,@currentDay)
end
commit transaction