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
end
For more on the new time data type check books on line here.
Subscribe in a reader" />
