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
USE [SS64_DATA_WAREHOUSE] GO 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 BEGIN -- 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) END -- 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
Calendar Table - with Year, Month, Week, weekday etc
Sources/Linkage: weekday calculation, persisted columns, Date Dimension using computed columns