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
I like your blog, I read this blog please update more content on hacking,
ReplyDeleteTableau Online Training