How-to: Create Date Dimension

The script below creates and populates a date table containing records at one day intervals for a range of years. The table contains a single datetime column and many other columns created with calculated attributes. All the additional columns are PERSISTED in the table for better performance. This make it convenient to get various commonly used date attributes from the data warehouse without having to program and test the same logic in many reports/applications.

First create an empty table (called dimDates) with this Create Table script

Delete FROM DimDates
-- declare variables to hold the start and end date
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @LoopDate datetime
-- Assign values to the start date and end date we want our reports to 
-- cover, take into account future reporting needs.
SET @StartDate = CAST('1995-01-01T00:00:00.000' AS datetime)
SET @EndDate = CAST('2045-12-31T00:00:00.000' AS datetime)
SET @LoopDate = @StartDate

-- Use a while loop to increment from the start to the end date
WHILE @LoopDate <= @EndDate
     -- add a record into the date dimension table for this date
    INSERT INTO DimDates (ActualDate) VALUES (@LoopDate)  
     -- increment the LoopDate by 1 day
    SET @LoopDate = DateAdd(day, 1, @LoopDate)

-- check how this looks in the table
SELECT * FROM DimDates order by [ActualDate]

“I don't understand how a woman can leave the house without fixing herself up a little - if only out of politeness. And then, you never know, maybe that's the day she has a date with destiny. And it's best to be as pretty as possible for destiny” ~ Coco Chanel


Time Dimension
Calendar Table - with Year, Month, Week, weekday etc
Sources/Linkage: weekday calculation, persisted columns, Date Dimension using computed columns

Copyright © 1999-2023
Some rights reserved