Time (Not Date) Dimension Table SQL code

I am seeing more call for Time Dimensions. By that I mean Time-of-Day Dimensions. So now I have to retrain myself to call Date Dimensions “Date” and Time Dimensions “Time”.

As a follow up to this post, here’s some DDL and a quick routine to generate a time of day table. The resolution is to the second, which so far has proved sufficient for my clients’ purposes.


SET nocount ON
CREATE TABLE [dbo].[dim_Time](
  [TimeId] [int] NOT NULL,
  [Time] [time](7) NULL,
  [Hours] [tinyint] NULL,
  [Minutes] [tinyint] NULL,
  [Seconds] [tinyint] NULL,
  [TimeId] ASC

TRUNCATE TABLE [dbo].[dim_Time]
SELECT * FROM dbo.dim_time

DECLARE @second1 INT = 0
DECLARE @currtime TIME

WHILE @second1 < 86400
      SELECT @second1 = @second1 + 1
      SELECT @currtime = Dateadd(ss, @second1, ’00:00.00′)
      —  SELECT   
      –DatePart(hh, @currtime) * 10000 + 
      –DatePart(mi, @currtime) * 100 +
      –DatePart(ss, @currtime) AS TimeID
      –, @currtime AS [Time]
      –, DatePart(hh, @currtime) AS [Hours]
      –, DatePart(mi, @currtime) AS [Minutes]
      –, DatePart(ss, @currtime) AS [Seconds]
      INSERT dbo.dim_time
      VALUES (
              Datepart(hh, @currtime) * 10000 + 

              Datepart(mi, @currtime) * 100 + 
              Datepart(ss, @currtime),
               Datepart(hh, @currtime),
               Datepart(mi, @currtime),
               Datepart(ss, @currtime) )
      IF @second1 % 1000 = 0
            SELECT @msg = ‘Now Processing ‘ + CAST(@second1 AS VARCHAR(12))
            PRINT @msg

Leave a Reply

Your email address will not be published.