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

Excellent example of code. I was able to use it to create the appropriate fields I needed.