SQL Server Advent Calendar 2 – Time

  • Article

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.

Technorati Tags: SQL server 2008,datetime,time data types