SQL Script to populate Date Dimension table in the Data warehouse.

IF OBJECT_ID('tempdb..##Dates') IS NOT NULL
	DROP TABLE ##Dates

  CREATE TABLE ##Dates(DateValue Date, 
                      YearValue INT, 
					  MonthValue INT, 
					  DayValue INT, 
					  WeekDayValue INT, 
					  MonthValueName VARCHAR(20),
					  WeekDayValueName VARCHAR(20))

  DECLARE @start DATE = '2015-01-01'
  WHILE @start < GETDATE()
  BEGIN
	INSERT INTO  ##Dates(DateValue,
	                     YearValue, 
						 MonthValue, 
						 DayValue, 
						 WeekDayValue, 
						 MonthValueName,
						 WeekDayValueName )
	VALUES(@start,
	        DATEPART(YY,@start),
			DATEPART(mm,@start),
			DATEPART(dd,@start), 
			DATEPART(dw,@start), 
			DATENAME(mm,@start),
			DATENAME(dw,@start))
	
	SET @start = DATEADD(dd,1,@start)
  END

4 thoughts on “SQL Script to populate Date Dimension table in the Data warehouse.”

1. omg yes! Thank you brotha!

   Reply

   1. welcome 🙂

      Reply

2. Hello,
   I have created below script for same without loop (using CTE).

   This is simple script to create to populate date table for data warehouse.

   IF OBJECT_ID(‘tempdb..##Dates’) IS NOT NULL
   DROP TABLE ##Dates

   CREATE TABLE ##Dates(DateValue Date,
   YearValue INT,
   MonthValue INT,
   DayValue INT,
   WeekDayValue INT,
   MonthValueName VARCHAR(20),
   WeekDayValueName VARCHAR(20))

   DECLARE @start DATE = ‘2019-01-01’

   ;with cte as (

   select @start as date_
   union all
   select dateadd(dd,1 ,date_)
   from cte
   where date_ <getdate()
   )
   INSERT INTO ##Dates(DateValue,
   YearValue,
   MonthValue,
   DayValue,
   WeekDayValue,
   MonthValueName,
   WeekDayValueName )
   select date_,
   DATEPART(YY,date_),
   DATEPART(mm,date_),
   DATEPART(dd,date_),
   DATEPART(dw,date_),
   DATENAME(mm,date_),
   DATENAME(dw,date_)
   from cte OPTION (maxrecursion 0)

   select * from ##Dates

   Reply

   1. This is awesome. Thank you!

      Reply