In one of my current data warehouse projects I wanted to use a time dimension. The time dimension structure I wanted was very similar to the one available with AdventureWorksDW but it has the date only till year 2004.

I had written a simple SP to populate the time fields. If anyone has a similar requirement you can try it.

---Populate Time dimension of AdventureWorksDW sample database with latest
--- You can either Create a new time dimension table or Use the table available in
--- AdventureWorksDW sample database of SQL Server 2005. Download from codeplex.com
set datefirst 1
declare
@starting_dt datetime
,@ending_dt datetime
,@cntr_day datetime
,@diff int
,@cntr int

Select
@starting_dt ='2009-01-01'
,@ending_dt = '2009-01-31'
,@cntr = 0

select
@diff = datediff(dd,@starting_dt,@ending_dt)

while @cntr <= @diff
begin
select @cntr_day = dateadd(dd,@cntr,@starting_dt)
insert into [DimDate]
(FullDateAlternateKey,
DayNumberOfWeek ,
EnglishDayNameOfWeek,
DayNumberOfMonth,
DayNumberOfYear,
WeekNumberOfYear,
EnglishMonthName,
MonthNumberOfYear,
CalendarQuarter,
CalendarYear,
CalendarSemester)
select
@cntr_day
,datepart(dw,@cntr_day)
,case datepart(dw,@cntr_day)
when 1 then 'Monday'
when 2 then 'Tuesday'
when 3 then 'Wednesday'
when 4 then 'Thursday'
when 5 then 'Friday'
when 6 then 'Saturday'
when 7 then 'Sunday'
end
,datepart(day,@cntr_day)
,datepart(dy,@cntr_day)
,datepart(wk,@cntr_day)
,case datepart(mm,@cntr_day)
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end
,datepart(mm,@cntr_day)
,datepart(qq,@cntr_day)
,datepart(yy,@cntr_day)
,case (datepart(mm,getdate()))
when 1 then 1
when 2 then 1
when 3 then 1
when 4 then 1
when 5 then 1
when 6 then 1
else 2
end
set @cntr = @cntr + 1
end

--And to get the fiscal properties in your time dimension check this one
http://www.sqlteam.com/article/returning-a-week-number-for-any-given-date-and-starting-fiscal-month