Script for creating DimTime Dimension Table and populating data

The posts I made on date dimension table (Script for populating Date Dimension with Financial Year and Loading Data Script for Snowflake type Date Dimension) had a good response and few asked about the same for Time related table. This is one of the old scripts I used for populating Time Dimension, it maintains up to the second but if you need more, the script can be easily change.

Here is the code for creating the table and populating data.

view plainprint?

1. CREATE TABLE dbo.Time  
2. (  
3.  TimeKey int identity(1,1)   
4.  , Constraint pk_Time primary key (TimeKey)  
5.  , [Hour] smallint not null  
6.  , [24Hour] smallint not null  
7.  , [Minute] smallint not null  
8.  , [Second] smallint not null  
9.  , [Time] char(10) not null  
10.  , [24Time] char(8) not null  
11.  , HourBucket char(9)  
12. );  
13. GO  
14.   
15. DECLARE @Hour smallint = 0  
16. DECLARE @Minute smallint = 0  
17. DECLARE @Second smallint = 0  
18.   
19. WHILE (@Hour < 24)  
20. BEGIN  
21.   
22.  WHILE (@Minute < 60)  
23.  BEGIN  
24.   
25.   WHILE (@Second < 60)  
26.   BEGIN  
27.   
28.    INSERT INTO dbo.[Time]  
29.     ([Hour], [24Hour], [Minute], [Second], [Time], [24Time], HourBucket)  
30.    VALUES  
31.     (CASE WHEN @Hour > 12 THEN @Hour - 12 ELSE @Hour END  
32.     , @Hour, @Minute, @Second  
33.     , REPLICATE('0', 2 - LEN(CASE WHEN @Hour > 12 THEN @Hour - 1 ELSE @Hour END)) + CONVERT(varchar(2), CASE WHEN @Hour > 12 THEN @Hour - 1 ELSE @Hour END)   
34.      + ':' + REPLICATE('0', 2 - LEN(@Minute)) + CONVERT(varchar(2), @Minute)   
35.      + ':' + REPLICATE('0', 2 - LEN(@Second)) + CONVERT(varchar(2), @Second)   
36.      + CASE WHEN @Hour > 12 THEN 'PM' ELSE 'AM' END  
37.     , REPLICATE('0', 2 - LEN(@Hour)) + CONVERT(varchar(2), @Hour)   
38.      + ':' + REPLICATE('0', 2 - LEN(@Minute)) + CONVERT(varchar(2), @Minute)   
39.      + ':' + REPLICATE('0', 2 - LEN(@Second)) + CONVERT(varchar(2), @Second)   
40.     , '[' + REPLICATE('0', 2 - LEN(@Hour)) + CONVERT(varchar(2), @Hour)   
41.      + ' - '   
42.      + REPLICATE('0', 2 - LEN(@Hour+1)) + CONVERT(varchar(2), @Hour+1) + ']')  
43.    SET @Second += 1;  
44.   END  
45.   SET @Second = 0;  
46.   SET @Minute += 1;  
47.  END  
48.  SET @Minute = 0;  
49.  SET @Hour += 1;  
50. END  
51. GO  
52.   
53. SELECT * FROM dbo.[Time];  

CREATE TABLE dbo.Time
(
 TimeKey int identity(1,1) 
 , Constraint pk_Time primary key (TimeKey)
 , [Hour] smallint not null
 , [24Hour] smallint not null
 , [Minute] smallint not null
 , [Second] smallint not null
 , [Time] char(10) not null
 , [24Time] char(8) not null
 , HourBucket char(9)
);
GO

DECLARE @Hour smallint = 0
DECLARE @Minute smallint = 0
DECLARE @Second smallint = 0

WHILE (@Hour < 24)
BEGIN

 WHILE (@Minute < 60)
 BEGIN

  WHILE (@Second < 60)
  BEGIN

   INSERT INTO dbo.[Time]
    ([Hour], [24Hour], [Minute], [Second], [Time], [24Time], HourBucket)
   VALUES
    (CASE WHEN @Hour > 12 THEN @Hour - 12 ELSE @Hour END
    , @Hour, @Minute, @Second
    , REPLICATE('0', 2 - LEN(CASE WHEN @Hour > 12 THEN @Hour - 1 ELSE @Hour END)) + CONVERT(varchar(2), CASE WHEN @Hour > 12 THEN @Hour - 1 ELSE @Hour END) 
     + ':' + REPLICATE('0', 2 - LEN(@Minute)) + CONVERT(varchar(2), @Minute) 
     + ':' + REPLICATE('0', 2 - LEN(@Second)) + CONVERT(varchar(2), @Second) 
     + CASE WHEN @Hour > 12 THEN 'PM' ELSE 'AM' END
    , REPLICATE('0', 2 - LEN(@Hour)) + CONVERT(varchar(2), @Hour) 
     + ':' + REPLICATE('0', 2 - LEN(@Minute)) + CONVERT(varchar(2), @Minute) 
     + ':' + REPLICATE('0', 2 - LEN(@Second)) + CONVERT(varchar(2), @Second) 
    , '[' + REPLICATE('0', 2 - LEN(@Hour)) + CONVERT(varchar(2), @Hour) 
     + ' - ' 
     + REPLICATE('0', 2 - LEN(@Hour+1)) + CONVERT(varchar(2), @Hour+1) + ']')
   SET @Second += 1;
  END
  SET @Second = 0;
  SET @Minute += 1;
 END
 SET @Minute = 0;
 SET @Hour += 1;
END
GO

SELECT * FROM dbo.[Time];