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

· 1 min read

This is a simple script l wrote in SQL server 2012 which can be used to populate the Date table for use in a data warehouse. It provides components of the date which can be used in any situation.

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

Share this post