Time Dimension

After posting script for Date Dimension, I thought of sharing script for Time Dimension as well. Some times we need to drill down the Cube data by time hierarchy e.g. AM/PM-->Hrs-->Min. Below script will create Time dimension and solve the purpose.

USE [DatabaseName]
GO

IF OBJECT_ID('DimTime') IS NOT NULL
DROP TABLE DimTime
GO


CREATE TABLE [dbo].[DimTime]
(
   [DimTimeSK] [int] NOT NULL,
   [Time] [varchar](11) NOT NULL,
   [Time24] [varchar](8) NOT NULL,
   [HourName] [varchar](5),
   [MinuteName] [varchar](8),
   [Hour] [tinyint],
   [Hour24] [tinyint],
   [Minute] [tinyint],
   [Second] [int],
   [AM] [char](2)
) ON [PRIMARY]

GO


DECLARE
   @DimTimeSK int,@Date datetime, @AM char(2),
   @hour24 tinyint, @hour tinyint,
   @minute tinyint, @second int

SET @DimTimeSK = 0

WHILE @DimTimeSK < (60*60*24)
BEGIN
   SET @DimTimeSK = @DimTimeSK + 1
   SET @Date = DATEADD(second,@DimTimeSK,convert(datetime, '1/1/2007'))
   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
   (
      [DimTimeSK]
      ,[Time]
      ,[Time24]
      ,[HourName]
      ,[MinuteName]
      ,[Hour]
      ,[Hour24]
      ,[Minute]
      ,[Second]
      ,[AM]
   )

   SELECT
   @DimTimeSK AS [DimTimeSK]
   ,right('0'+ convert(varchar,@hour),2) + ':' +
   right('0'+ convert(varchar,@minute),2) + ':' +
   right('0'+ convert(varchar,@second),2) + ' ' + @AM AS [Time]
   ,convert(varchar,@Date,108) [Time24]
   ,right('0' + convert(varchar,@hour),2) + ' ' + @AM AS [HourName]
   ,right('0' + convert(varchar,@hour),2) + ':' +
   right('0' + convert(varchar,@minute),2)+ ' ' + @AM AS [MinuteName]
   ,@hour AS [Hour]
   ,@hour24 AS [Hour24]
   ,@minute AS [Minute]
   ,@second AS [Second]
   ,@AM AS [AM]
END
GO