DimTime Dimension

The seed script for a time-of-day dimension in my SSAS models. It loops through all 86,400 seconds of a day and inserts one row per second into dbo.DimTime, carrying both 12-hour and 24-hour representations: Time and HourName/MinuteName in HH:MM:SS AM/PM form, Time24 as the raw 24-hour string, zero-padded hour/minute/second columns, an AM flag, and a MinuteKey (hours*60+minutes) handy for grouping at minute granularity.

delete from DimTime

declare @DimTimeKey int, @Date datetime, @AM char(2), 
    @hour24 tinyint, @hour tinyint, @minute tinyint, @second int
set @DimTimeKey = 0

while @DimTimeKey < (60*60*24)
begin
    set @Date = DATEADD(second,@DimTimeKey,convert(datetime, '1/1/2012'))
    set @AM = right(convert(varchar,@Date,109),2)
    set @hour24 = DATEPART(hour, @Date)
    set @hour = case when @AM = 'PM' then @hour24 - 12 else @hour24 end
    set @minute = DATEPART(minute, @Date)
    set @second = DATEPART(second, @Date)



    insert into dbo.DimTime(DimTimeKey,Time,Time24,HourName, 
                MinuteName,MinuteKey,Hour,Hour24,Minute,Second,AM)
    select    @DimTimeKey, 
            Time = right('0' + convert(varchar,@hour),2) + ':' + right('0' + convert(varchar,@minute),2) + ':' + right('0' + convert(varchar,@second),2) + ' ' + @AM,
            Time24 = convert(varchar,@Date,108), 
            HourName = right('0' + convert(varchar,@hour),2) + ' ' + @AM,
            MinuteName = right('0' + convert(varchar,@hour),2) + ':' + right('0' + convert(varchar,@minute),2)+ ' ' + @AM,
            MinuteKey = (@hour24*60) + @minute,
            (CASE WHEN @hour < 10 THEN '0' ELSE '' END) + cast(@hour as varchar(2)),
            (CASE WHEN @hour24 < 10 THEN '0' ELSE '' END) + cast(@hour24 as varchar(2)),
            (CASE WHEN @minute < 10 THEN '0' ELSE '' END) + cast(@minute as varchar(2)),
            (CASE WHEN @second < 10 THEN '0' ELSE '' END) + cast(@second as varchar(2)),
            @AM
    set @DimTimeKey = @DimTimeKey + 1
end