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

SQL Server Advent Calendar 2 – Time

  • Comments 1
  • Likes

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
end

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment