Time Hour Dimension

/***************************************
Script: Creates and Populates an Hour Dimension Table 

Note: This script does hours and minutes. If seconds are needed,
then modify the datepart on the last line that increments the date.
Obviously the table will be bigger when you include seconds.

Author: Ibrahim Hafidh 
Date: 10/11/2005
****************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dimHour]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dimHour]
GO

CREATE TABLE dimHour
(
    HourID       INT  IDENTITY (1, 1)   NOT NULL    PRIMARY KEY
,   TheDate      DATETIME   NOT NULL
,   TheTime  Datetime   NOT NULL
,   MilitaryHour INT NOT NULL
,   StandardHour  INT NOT NULL
,   TheMinute    INT NOT NULL
,   TheSecond    INT NOT NULL
,   Standard  varchar(2) NOT NULL
)


DECLARE    @startdate  DATETIME
DECLARE    @enddate    DATETIME
DECLARE    @date       DATETIME

SET        @startdate  =    '1/1/2005 12:00:00 AM'   
SET        @enddate    =    '1/1/2005 23:59:59 PM'  
SET        @date       =     @startdate

WHILE    @date <= @enddate
BEGIN
    INSERT INTO    dimHour (TheDate, TheTime, MilitaryHour, StandardHour, TheMinute, TheSecond, Standard)
    VALUES (
     @date                                                             --TheDate
    ,    convert(nvarchar(11), @date, 114)                                 --Time format   
    ,    DATEPART(hh, @date)                                               --Military Hour
    , CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], @Date) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], @Date) - 12))
               WHEN DATEPART([hour], @Date) = 0 THEN '12'
               ELSE CONVERT(varchar(2), DATEPART([hour], @Date))
          END)   -- Standard Hour
    ,    DATEPART(mi, @date)                                               --Minute
    ,    DATEPART(ss, @date)                                               --Second 
    , CASE WHEN DATEPART(hh, @date) between 0 and 11 THEN 'AM' ELSE 'PM' END   
    )


    SET  @date  =    DATEADD(mi, 1, @date) -- ** Modify the datepart to ss if seconds are needed

END