I needed to create a really simple time of day dimension the other day, so I thought I’d share my solution. My setup:
- SQL Server 2008
First off create the table. This is a basic table, and looks as follows:
CREATE TABLE [dbo].[DimTimeOfDay](
[DimTimeOfDayID] [int] IDENTITY(1,1) NOT NULL,
[Hour] [tinyint] NULL,
[Minute] [tinyint] NULL,
[Name] [varchar](5) NULL,
CONSTRAINT [PK_DimTimeOfDay] PRIMARY KEY CLUSTERED
(
[DimTimeOfDayID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
And now the script to populate it.
DECLARE @Hour INT
DECLARE @Minute INT
SET @Hour = 0
SET @Minute = 0;
WHILE @Hour < 24
BEGIN
WHILE @Minute < 60
BEGIN
INSERT INTO DimTimeOfDay (Hour, Minute) VALUES (@Hour, @Minute)
SET @Minute = @Minute + 1
END
SET @Minute = 0;
SET @Hour = @Hour + 1
UPDATE DimTimeOfDay SET Name = RIGHT(‘00’ + CAST(Hour AS VARCHAR), 2) + ‘:’ + RIGHT(‘00’ + CAST(Minute AS VARCHAR), 2)
END
Nothing groundbreaking, but perhaps this will save someone out there a couple of minutes. Enjoy.
