Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server Advent Calendar 2 – Time

Day 2 of my virtual advent calendar, about stuff I like in SQL Server 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

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