SQl server 2008 script to create and populate a date dimension table

This script was based and improved upon an idea (with some bugs) on sqlservercentral.com. I put it here so that I can easily reuse it.

CREATE TABLE dbo.DateDim(
DateID int IDENTITY(1,1) NOT NULL,

ActualDate date NOT NULL,

[Year] smallint NOT NULL,

[Quarter] smallint NOT NULL,

[Month] smallint NOT NULL,

[Week] smallint NOT NULL,

[DayofYear] smallint NOT NULL,

[DayofMonth] smallint NOT NULL,

[DayofWeek] smallint NOT NULL,

IsWeekend bit NOT NULL,

IsHoliday bit NOT NULL,

CalendarWeek smallint NOT NULL,

BusinessYearWeek smallint NOT NULL,

LeapYear bit NOT NULL,
CONSTRAINT PK_DateDim PRIMARY KEY CLUSTERED
(

DateID ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

) ON PRIMARY

GO
 ALTER TABLE dbo.DateDim WITH CHECK ADD CONSTRAINT FK_DateDim_DateDim FOREIGN KEY(DateID)

REFERENCES dbo.DateDim (DateID)

GO



ALTER TABLE dbo.DateDim CHECK CONSTRAINT FK_DateDim_DateDim

GO
 CREATE PROCEDURE dbo.sp_createTimeDim AS


--declare variables
DECLARE @DT DATE
DECLARE @YEAR smallint
DECLARE @QUARTER smallint
DECLARE @MONTH smallint
DECLARE @WEEK smallint
DECLARE @DayofYear smallint
DECLARE @DayofMonth smallint
DECLARE @DayofWeek smallint
DECLARE @IsWeekend BIT
DECLARE @IsHoliday BIT
DECLARE @CalendarWeek smallint
DECLARE @BusinessYearWeek INT
DECLARE @LeapYear BIT

--initialize variables


SELECT @BusinessYearWeek =0
SELECT @CalendarWeek = 1
SELECT @LeapYear =0

--the starting date for the date dimension
SELECT @DT = '2012-01-01'

--start looping, stop at ending date

WHILE (@DT <= '2020-12-31')


BEGIN
--get information about the data

SET @IsWeekend =0
SET @YEAR = DATEPART (YEAR, @DT)
SET @QUARTER = DATEPART (QUARTER, @DT)
SET @MONTH = DATEPART (MONTH , @DT)
SET @WEEK = DATEPART (WEEK , @DT)
SET @DayofYear = DATEPART (DY , @DT)
SET @DayofMonth = DATEPART (DAY , @DT)
SET @DayofWeek = DATEPART (DW , @DT)


--note if weeknd or not


IF ( @DayofWeek = 1 OR @DayofWeek = 7 )
BEGIN
SELECT @IsWeekend = 1
END


--add 1 every time we start a new week


IF ( @DayofWeek = 1)

BEGIN
SELECT @CalendarWeek = @CalendarWeek +1

END


--add business rule (need to know complete weeks in a year, so a partial week in new year set to 0)


IF ( @DayofWeek != 1 AND @DayofYear = 1)

BEGIN

SELECT @BusinessYearWeek = 0

END


IF ( @DayofWeek = 1)

BEGIN

SELECT @BusinessYearWeek = @BusinessYearWeek +1

END

--add business rule (start counting business weeks with first complete week)

IF (@BusinessYearWeek =53)

BEGIN

SELECT @BusinessYearWeek = 1

END

--check for leap year


IF ((@YEAR % 4 = 0) AND (@YEAR % 100 != 0 OR @YEAR % 400 = 0))

SELECT @LeapYear =1

ELSE SELECT @LeapYear =0

--insert values into Date Dimension table

INSERT Mgmt.DateDim (ActualDate, Year, Quarter, Month, Week, DayofYear, DayofMonth, DayofWeek, IsWeekend, CalendarWeek, BusinessYearWeek, LeapYear, IsHoliday)

VALUES (@DT, @YEAR, @QUARTER, @MONTH, @WEEK, @DayofYear, @DayofMonth, @DayofWeek, @IsWeekend, @CalendarWeek, @BusinessYearWeek, @LeapYear, @IsHoliday)

--increment the date one day


SELECT @DT = DATEADD(DAY, 1, @DT)

END
GO


DECLARE @RC int


EXECUTE @RC = dbo.sp_createTimeDim


GO