The procedure below creates and populates a time table containing records at one minute intervals for a single day. The time range is 00:00:00 through 23:59:00. The table returned contains a single datetime column along with several other CHAR and INT columns containing formatted attributes of each datetime. This make it convenient to get various commonly used time attributes from the data warehouse without having to program and test the same logic in many reports/applications.
First create an empty table (called dimTimes) with this Create Table script
USE [SS64_DATA_WAREHOUSE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[userproc_GenerateDimTimes] AS
BEGIN
-- declare variables to hold the start and end date
DECLARE @Startint INTEGER
DECLARE @Endint INTEGER
-- Assign values to the start and end numbers, there are 1440 seconds in a day
SET @Startint = 0
SET @Endint = 1439
DECLARE @LoopNum integer
SET @LoopNum = @Startint
-- Loop once for each second in a day
WHILE @LoopNum <= @Endint
BEGIN
-- add a record to the time dimension table
INSERT INTO dimTimes (CLOCKTIME,HOUR24,MINUTE24,TIME24,MINUTE12,HOUR12,HOUR12_LONG,TIME12,TIME12_LONG,AMPM) VALUES (
Cast(RIGHT('0' + Cast((Floor(@LoopNum/60)) AS VARCHAR(2)), 2) + ':' + RIGHT('0' + Cast(@LoopNum - (Floor(@LoopNum/60) * 60) AS VARCHAR(2)), 2) AS SMALLDATETIME),
Cast((Floor(@LoopNum/60)) as int)
Cast(@LoopNum - (Floor(@LoopNum/60) * 60) as int),
Cast(RIGHT('0' + Cast(Floor(@LoopNum/60) AS VARCHAR(2)), 2) + ':' + RIGHT('0' + Cast(@LoopNum - (Floor(@LoopNum/60) * 60) AS VARCHAR(2)), 2) + ':00' as char),
Cast(@LoopNum - (Floor(@LoopNum/60) * 60) as int),
Cast(CASE WHEN ((Floor(@LoopNum/60)) % 12) <> 0 THEN ((Floor(@LoopNum/60)) % 12) ELSE 12 END as int),
Cast(cast(CASE WHEN ((Floor(@LoopNum/60)) % 12) <> 0 THEN ((Floor(@LoopNum/60)) % 12) ELSE 12 END as varchar) + ' ' + CASE WHEN (Floor(@LoopNum/60)) < 12 THEN 'AM' ELSE 'PM' END AS VARCHAR(5))
Cast(RIGHT('0' + Cast(CASE WHEN ((Floor(@LoopNum/60)) % 12) <> 0 THEN ((Floor(@LoopNum/60)) % 12) ELSE 12 END AS VARCHAR(2)), 2) + ':' + RIGHT('0' + Cast((@LoopNum - (Floor(@LoopNum/60) * 60)) AS VARCHAR(2)), 2) + ':00' as char),
Cast(RIGHT('0' + Cast(CASE WHEN ((Floor(@LoopNum/60)) % 12) <> 0 THEN ((Floor(@LoopNum/60)) % 12) ELSE 12 END AS VARCHAR(2)), 2) + ':' + RIGHT('0' + Cast((@LoopNum - (Floor(@LoopNum/60) * 60)) AS VARCHAR(2)), 2) + ':00 ' + CASE WHEN (Floor(@LoopNum/60)) < 12 THEN 'AM' ELSE 'PM' END as char),
Cast(CASE WHEN (Floor(@LoopNum/60)) < 12 THEN 'AM' ELSE 'PM' END as char
Cast(CASE WHEN (@LoopNum) = 0 THEN 'Midnight' WHEN (@LoopNum) = 720 THEN 'Midday' WHEN (Floor(@LoopNum/60)) < 12 THEN 'AM' ELSE 'PM' END as char)
)
SET @LoopNum = @LoopNum + 1
END
-- Add values for Unknown
SET IDENTITY_INSERT dimTimes ON
INSERT INTO dimTimes (TIMESK,CLOCKTIME,HOUR24,MINUTE24,TIME24,MINUTE12,HOUR12,HOUR12_LONG,TIME12,TIME12_LONG,AMPM) VALUES (
-1,'00:00:00',-1,-1,'Unknown',-1,-1,'Unk','Unknown','Unknown','Unknown')
SET IDENTITY_INSERT dimTimes OFF
Return
END
-- Empty the table (in case previously populated)
DELETE FROM dbo.DimTimes
Go
-- Reseed the identity column
DBCC CHECKIDENT('DimTimes', RESEED, 0)
Go
-- Run the procedure
Exec userproc_GenerateDimTimes
Go
Related
“The real minimum wage is zero” ~ Thomas Sowell