DimTime - Time Dimension

The below script is helpful to create attributes for Time dimension used in Business Intelligence:

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