Common Data Mart/Data Warehouse Code for creating Date and Time Dimensions. Note the Time Dimension is distinct from Date Dimension as it tracks time of day and time durations. The bucket fields are useful for analysis of data in blocks of time or duration and can be used as the buckets in a histogram.

CreateDateDimension.sql

DROP TABLE [dbo].[DimDate]
GO
CREATE TABLE [dbo].[DimDate] (
	[DateKey] [int] NOT NULL PRIMARY KEY,
	[FullDate] [date] NOT NULL,
	[Year] [smallint] NOT NULL,
	[Quarter] [tinyint] NOT NULL,
	[MonthOfYear] [tinyint] NOT NULL,
	[MonthName] [varchar](10) NOT NULL,
	[WeekOfYear] [tinyint] NOT NULL,
	[DayOfMonth] [tinyint] NOT NULL,
	[DayOfWeek] [tinyint] NOT NULL,
	[DayName] [varchar](10) NOT NULL,
	[IsMonthEnd] bit NOT NULL DEFAULT 0,
	[IsWorkday] bit NOT NULL DEFAULT 1,
	[IsHoliday] bit NOT NULL DEFAULT 0
) ON [PRIMARY]
CREATE INDEX IDX_DimDate_FullDate ON DimDate(FullDate) INCLUDE (IsMonthEnd, IsWorkday, IsHoliday);
CREATE INDEX IDX_DimDate_Calendar ON DimDate(Year, Quarter, MonthOfYear) INCLUDE (MonthName);
CREATE INDEX IDX_DimDate_Weeks ON DimDate(Year, WeekOfYear, DayOfWeek) INCLUDE (DayName);
GO

CreateTimeDimension.sql

DROP TABLE [dbo].[DimTime]
GO
CREATE TABLE [dbo].[DimTime] (
  [TimeKey] [int] NOT NULL PRIMARY KEY,
	[Hour] [tinyint] NOT NULL,
	[Minute] [tinyint] NOT NULL,
	[TimeBucket] [smallint] NOT NULL DEFAULT 0,
	[TotalSeconds] [int] NOT NULL DEFAULT 0,
	[TotalMilliseconds] [bigint] NOT NULL DEFAULT 0,
	[DurationBucket] [int] NOT NULL DEFAULT 0
) ON [PRIMARY]
CREATE INDEX IDX_DimTime_Bucket ON DimTime(TimeBucket);
CREATE INDEX IDX_DimTime_TotalSeconds ON DimTime(TotalSeconds) INCLUDE (DurationBucket);
CREATE INDEX IDX_DimTime_TotalMilliseconds ON DimTime(TotalMilliseconds) INCLUDE (DurationBucket);
GO

FillDateDimension.sql

DECLARE @startDate as datetime,@endDate as datetime;
SET @startDate = '2008-01-01T00:00:00';
SET @endDate = '2016-01-01T00:00:00';
 
while @startDate<@endDate
begin
  INSERT INTO DimDate([DateKey]
		,[FullDate]
		,[Year]
		,[Quarter]
		,[MonthOfYear]
		,[MonthName]
		,[WeekOfYear]
		,[DayOfMonth]
		,[DayOfWeek]
		,[DayName]
		,[IsMonthEnd]
		,[IsWorkday])
	SELECT CAST(CONVERT(char(8), @startDate, 112) AS INT) as DateKey
		,@startDate
		,DATEPART(year, @startDate) as Year
		,DATEPART(quarter, @startDate) as Quarter
		,DATEPART(month, @startDate) as MonthOfYear
		,DATENAME(month, @startDate) as MonthName
		,DATEPART(week, @startDate) as WeekOfYear
		,DATEPART(day, @startDate) as DayOfMonth
		,DATEPART(weekday, @startDate) as DayOfWeek
		,DATENAME(weekday, @startDate) as DayName
		,(CASE WHEN DATEPART(day, DATEADD(day, 1, @startDate)) = 1 THEN 1 ELSE 0 END) AS IsMonthEnd
		,(CASE WHEN DATEPART(weekday, @startDate) = 1 OR DATEPART(weekday, @startDate) = 7 THEN 0 ELSE 1 END) as IsWorkDay
	SET @startDate = DATEADD(day, 1, @startDate);
end

FillTimeDimension.sql

DECLARE @startTime as datetime,@endTime as datetime, @timebucket as int, @i as int, @seconds as int, @milliseconds as bigint, @durationbucket as int, @j as int;
SET @startTime = '2013-01-01T00:00:00';
SET @endTime = '2013-01-02T00:00:00';
SET @timebucket = 1;
SET @i = 1;
SET @seconds = 0;
SET @milliseconds = 0;
SET @j = 1;
SET @durationbucket = 1;

while @startTime<@endTime
begin
  INSERT INTO DimTime (TimeKey, [Hour], [Minute], [TimeBucket], [TotalSeconds], [TotalMilliseconds], [DurationBucket]) 
	VALUES (CAST(REPLACE(CONVERT(char(8), @startTime, 108), ':', '') as INT), DATEPART(hour, @startTime), DATEPART(minute, @startTime), @timebucket, @seconds, @milliseconds, @durationbucket);

	SET @i = @i + 1;
	SET @j = @j + 1;
	SET @seconds = @seconds + 1;
	SET @milliseconds = @milliseconds + 100;

	IF @i > 300 -- 5 min buckets for clock time intervals
	BEGIN
		SET @i = 1;
		SET @timebucket = @timebucket + 1;
	END

	IF @j > 30 -- 30 sec buckets for duration intervals
	BEGIN
		SET @j = 1;
		SET @durationbucket = @durationbucket + 1;
	END
	
	SET @startTime = DATEADD(second, 1, @startTime);
end
GO