I was recently faced with the challenge of creating a calendar dimension with hourly grain for reporting on a Microsoft SQL Server data warehouse application

There are two parts to the solution. A script to create and populate a table and a function used to generate integer keys from dates.

The script.

/* Title: Script to create a dateTime dimension with 
hourly granularity.

 Author: 
 James Bayley james@elephantpm.com 
 http://www.elephantpm.com
 published at http://blog.jamesbayley.com
based on a script published by

 Joost van Rossum 
 http://microsoft-ssis.blogspot.co.uk/2011/01/create-and-fill-time-dimension.html

 Purpose:
 This script creates and populates a dateTime dimension 
(calendar dimension) with hourly grain. The key is 
 an integer of the form YYYYMMDDHH

 For each dateTime in your fact table you must introduce 
a similar integer key. For
 example if you have a "SaleDateTime" you must create 
"iSaleDateTime" which represents
 this dateTime as an integer of the form YYYYMMDDHH. 

 You use this table in a left out join with your fact tables 
to ensure that you get a record for
 each hour you want to report. You can of course aggregate at 
higher levels.

 Comments:
 This script is easily extended to include other columns 
such as financial year and local times
 or you may increase the granularity to minutes or seconds 
by adding additional loops

 Copyright: (C)
 Please include the author block above.

 Licence Creative Common Attribution 2.0
 http://creativecommons.org/licenses/by/2.0/uk/
 */
drop table dbo.ElephantPM_Dim_DateTime
go

-- Create your dimension table
-- Adjust to your own needs
Create Table dbo.ElephantPM_Dim_DateTime
(
 iDateTime int PRIMARY KEY CLUSTERED,
 DateTime dateTime,
 DateString varchar(10),
 HourOfDay int,
 Date date,
 Day int,
 DayofYear int,
 DayofWeek int,
 DayofWeekName varchar(10),
 Week int,
 Month int,
 MonthName varchar(10),
 Quarter int,
 Year int,
 IsWeekend bit,
 IsLeapYear bit
)

-- Declare and set variables for loop
Declare
@StartDate datetime,
@EndDate datetime,
@DateTime datetime,
@iHour int
-- Set the dates to match your data 
Set @StartDate = '2004/01/01'
Set @EndDate = '2018/12/31'
Set @DateTime = @StartDate

-- Loop through dates
WHILE @DateTime <=@EndDate
 BEGIN
 -- Check for leap year
 DECLARE @IsLeapYear BIT
 IF ((Year(@DateTime) % 4 = 0) AND (Year(@DateTime) % 100 != 0 OR Year(@DateTime) % 400 = 0))
 BEGIN
 SELECT @IsLeapYear = 1
 END
 ELSE
 BEGIN
 SELECT @IsLeapYear = 0
 END

 -- Check for weekend
 DECLARE @IsWeekend BIT
 IF (DATEPART(dw, @DateTime) = 1 OR DATEPART(dw, @DateTime) = 7)
 BEGIN
 SELECT @IsWeekend = 1
 END
 ELSE
 BEGIN
 SELECT @IsWeekend = 0
 END

 set @iHour = 0
 While @iHour <=23
 begin

 -- Insert record in dimension table
 INSERT Into ElephantPM_Dim_DateTime
 (
 [iDateTime],
 [DateTime],
 [Date],
 [DateString],
 [HourOfDay],
 [Day],
 [DayofYear],
 [DayofWeek],
 [Dayofweekname],
 [Week],
 [Month],
 [MonthName],
 [Quarter],
 [Year],
 [IsWeekend],
 [IsLeapYear]
 )
 Values
 (
 [dbo].[IntKeyForYYMMDDHH](@DateTime),
 @DateTime,
 convert(date,@DateTime),
 CONVERT(varchar(10), @DateTime, 6), -- See help for 6 explanation
 @iHour,
 Day(@DateTime),
 DATEPART(dy, @DateTime),
 DATEPART(dw, @DateTime),
 DATENAME(dw, @DateTime),
 DATEPART(wk, @DateTime),
 DATEPART(mm, @DateTime),
 DATENAME(mm, @DateTime),
 DATENAME(qq, @DateTime),
 Year(@DateTime),
 @IsWeekend,
 @IsLeapYear
 )
 select @DateTime = dateadd(hour,1,@DateTime)
 set @iHour = @iHour +1
 end
 END
GO

The function.

/****** Object: UserDefinedFunction [dbo].[IntKeyForYYMMDDHH] 
Script Date: 01/04/2013 14:37:18 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IntKeyForYYMMDDHH]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[IntKeyForYYMMDDHH]
GO
USE [fogbugz-development-copy]
GO
ET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
--Author: 
 --James Bayley james@elephantpm.com 
 -- http://www.elephantpm.com
 --published at http://blog.jamesbayley.com
 --Copyright: (C)
 --Please include the author block above.

 --Licence Creative Common Attribution 2.0
 --http://creativecommons.org/licenses/by/2.0/uk/
-- =============================================
CREATE FUNCTION [dbo].[IntKeyForYYMMDDHH] 
(
 -- Add the parameters for the function here
 @dateTime dateTime
)
RETURNS int
AS
BEGIN
 -- Declare the return variable here
 DECLARE @sYYYYMMDD nchar(8)
 declare @iHour int
 declare @sHour nchar(2)
 declare @sYYYYMMDDHH nchar(10)
 declare @iReturn int -- Add the T-SQL statements to compute the return value here

 select @sYYYYMMDD = convert(nchar,@dateTime,112)
 select @iHour = datepart(hour, @dateTime)
 if (@iHour < 10)
 Begin
 select @sHour = '0' + convert(nchar,@iHour)
 End
 else
 Begin
 select @sHour = convert(nchar,@iHour)
 End
 select @sYYYYMMDDHH = @sYYYYMMDD + @sHour 
 select @iReturn = convert(int,@sYYYYMMDDHH) -- Return the result of the function
 RETURN @iReturn 
END 

GO EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'This function is used to generate the integer key used to join fact tables to the dateTime dimension with hourly granularity.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'IntKeyForYYMMDDHH'
GO

(also known as date and time dimensions, date dimensions, dateTime dimension)