Andrew Fryer's Blog – creating a “time of day” dimension

Highlighting some of the pluses of SQL 2008

Following on from my previous post, in some data warehouses there is a separate dimension for time of day, so that demand through a day can be modelled. Storing time in SQL server 2005 was a bit of a cludge typically involving picking an arbitrary date (like 1/1/1900) and then tacking the time on to the end of that.  Now there’s a separate time data type so it’s easy to store the right data and create the time dimension using a script like this:

declare @time time = '00:00'
declare @timekey int = 0
declare @timegrain int =15

if not exists
    (select  * from sys.tables where name = 'dimTimeofday')
create table dimTimeofday( timekey int, TimeofDay time)
while @timekey < 1440 begin   
    insert into dimTimeofday(timekey,Timeofday) values (@timekey, @time)
    set @time = dateadd(minute,@timegrain,@time)
    set @timekey += @timegrain
end

For more on the new time data type check books on line here.

Andrew Fryer's Blog