Create and Populate Date Dimension

There are many Create and Populate Date Dimension post already available. However, here I have simplified the table structure and the populate data via stored procedure. Also This will answer how to extend the date range in AdventureWorksDW2012.

Table structure:

CREATE TABLE dbo.DimDate(
 [DateKey] [int] IDENTITY(1,1) NOT NULL,
 [FullDate] [datetime] NOT NULL,
 [DayNumberOfWeek] [tinyint] NOT NULL,
 [EnglishDayNameOfWeek] [varchar](20) NOT NULL,
 [DayNumberOfMonth] [tinyint] NOT NULL,
 [DayNumberOfYear] [smallint] NOT NULL,
 [WeekNumberOfYear] [tinyint] NOT NULL,
 [CalendarEnglishMonthName] [varchar](20) NOT NULL,
 [CalendarEnglishShortMonthName] [char](3) NULL,
 [CalendarMonthNumberOfYear] [smallint] NOT NULL,
 [CalendarQuarter] [tinyint] NOT NULL,
 [CalendarYear] [int] NOT NULL,
 [CalendarSemester] [tinyint] NOT NULL,
 [FiscalYear] [smallint] NULL,
 [FiscalSemester] [smallint] NULL,
 [FiscalQuarter] [smallint] NULL,
 CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED
 (
   [DateKey] ASC
 )
);

Stored Procedure to extend or populate the date dimension table records.

CREATE PROCEDURE insertDate
 @StartDate  DATETIME = NULL,
 @EndDate  DATETIME =NULL
AS
BEGIN 
SET IDENTITY_INSERT dbo.DimDate ON

WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO dbo.DimDate
(  DateKey
 ,FullDate
 ,DayNumberOfWeek
 ,EnglishDayNameOfWeek
 ,DayNumberOfMonth
 ,DayNumberOfYear
 ,WeekNumberOfYear
 ,CalendarEnglishMonthName
 ,CalendarEnglishShortMonthName
 ,CalendarMonthNumberOfYear
 ,CalendarQuarter
 ,CalendarYear
 ,CalendarSemester
 ,FiscalYear
 ,FiscalSemester
 ,FiscalQuarter
)

SELECT  
CONVERT(INTEGER, CONVERT(CHAR(10),  @StartDate, 112)) AS DateKey

, @StartDate AS FullDate

, DATEPART(dw, @StartDate) AS DayNumberOfWeek

, DATENAME(dw, @StartDate) AS EnglishDayNameOfWeek

, DAY (@StartDate) AS DayNumberOfMonth

, DATENAME(dayofyear, @StartDate) AS DayNumberOfYear

, DATENAME(week, @StartDate) AS WeekNumberOfYear

, DATENAME(mm, @StartDate) AS CalendarEnglishMonthName

, LEFT(DATENAME(mm, @StartDate),3) AS CalendarEnglishShortMonthName

, MONTH(@StartDate) AS CalendarMonthNumberOfYear

, DATENAME(quarter, @StartDate) AS CalendarQuarter

, YEAR(@StartDate) AS CalendarYear

, CASE
 WHEN DATENAME(quarter, @StartDate) <= 2 THEN 1 
 ELSE 2
  END AS CalendarSemester

, CASE
 WHEN DATEPART(m, @StartDate) >= 7 THEN YEAR(@StartDate) + 1 ELSE YEAR(@StartDate)
 END AS FiscalYear

, CASE
 WHEN DATENAME(quarter, @StartDate) >= 3 THEN 1 ELSE 2
 END AS FiscalSemester

, CASE
 WHEN MONTH(@StartDate) IN (7, 8, 9) THEN 1
 WHEN MONTH(@StartDate) IN (10, 11, 12) THEN 2
 WHEN MONTH(@StartDate) IN (1, 2, 3) THEN 3
 WHEN MONTH(@StartDate) IN (4, 5, 6) THEN 4
    END AS FiscalQuarter

SET @StartDate = DATEADD(dd, 1, @StartDate)
END
SET IDENTITY_INSERT dbo.DimDate OFF
END

To insert or extend the records, just run the SP with Start Date and End Date.


Cheers!