How To: Create Date Dimension Tables in SQL Server 2000/2005

SQL
May 12, 2007

I was writing an application today that needed a date dimension table and this was the script that i built. I don’t know if this going to be useful to anybody.. anyway, my main purpose of putting it in my blog is so that i can just go to my site and do a copy-and-paste when the time comes that I need to use this script again.


First we need to build our table schema. My table schema required an ID column, a Date column, a Year column, a Month column and a Day column. All fields where of int data type except for the Date column. Below is how the script for creating the table looks like.


[STEP 1 – Create Table Schema.sql]


–#############################################################
— Author: Keith Rull
— Script Date: May 11. 2007
— Description: This script creates the table schmema for
— our date dimension table.
–#############################################################
IF EXISTS (
                SELECT
                    *
                FROM
                    sys.objects
                WHERE
                        object_id = OBJECT_ID(N‘[dbo].[DateDimension]’)
                    AND type in (N‘U’)
            )
DROP TABLE [dbo].[DateDimension]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DateDimension](
    [DateID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Year] [int] NOT NULL,
    [Month] [int] NOT NULL,
    [Day] [int] NOT NULL,
CONSTRAINT [PK_DateDimension] PRIMARY KEY CLUSTERED
(
    [DateID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


*NOTE: Please be careful when using this script. A drop table script is include on the first few lines of the file. Please delete it if you dont need to delete the existing date dimension table


I built two scripts for creating a date dimension table. The first script is designed to be use with SQL Server 2000(or earlier) but will still work with SQL Server 2005. I created it by using a while loop instead of using a cursor which an officemate of mine suggested because i didn’t find any urgency to use cursors for this script. Below is the is the script I built:


[STEP 2 – POPULATE THE TABLE WITH DATES.sql]


–#############################################################
— Author: Keith Rull
— Script Date: May 11, 2007
— Description: Use this script if you are using SQL Server 2000
— or earlier. This works with SQL Server 2005 too.
–#############################################################

–declare our date variables
DECLARE @LowerBoundDate DATETIME
DECLARE @UpperBoundDate DATETIME
DECLARE @IteratingDate DATETIME

–set the initial dates
SET @LowerBoundDate = ‘1/1/1980’
SET @UpperBoundDate = ‘1/1/2040’

–create our temporary table
DECLARE @TempDateDimension TABLE
(
        Date DATETIME
    ,    [Year] INT
    ,    [Month] INT
    ,    [Day]    INT
)

–set the iterating date to the lowerbound date
SET @IteratingDate = @LowerBoundDate

–continue to loop until our iterating date is the same as the target date
WHILE @IteratingDate <= @UpperBoundDate
BEGIN
    –insert the date to our temporary table
    INSERT @TempDateDimension
    (
            Date
        ,    [Year]
        ,    [Month]
        ,    [Day]
    )
    VALUES
    (    
            @IteratingDate
        ,    Year(@IteratingDate)
        ,    Month(@IteratingDate)
        ,    Day(@IteratingDate)
    )
    –increase the value of our iterating date
    SET @IteratingDate = @IteratingDate + 1
END

–Insert the dates to our permanent Dimension table
INSERT INTO dbo.DateDimension
SELECT     
        Date
    ,    [Year]
    ,    [Month]
    ,    [Day]
FROM
    @TempDateDimension

–View the contents of the table
SELECT     
        Date
    ,    [Year]
    ,    [Month]
    ,    [Day]
FROM
    dbo.DateDimension WITH(NOLOCK)


The script above expects you to change the start and end date. What the script does is that it iterates from a lowerbound date to an upperbound date and inserts those dates into our DateDimension table. Pretty slick huh?


What’s cooler is that you can do the same script without using a while loop(or cursors) with one the new features of SQL Server 2005 called Common Table Expressions. The resulting script would look like this:


[STEP 2 – POPULATE THE TABLE WITH DATES(SQL 2005).sql]


–#############################################################
— Author: Keith Rull
— Script Date: May 11, 2007
— Description: Use this script if you want to use the new
— SQL Server 2005 featured called Commong Table Expressions.
— This script only works with SQL Server 2005
–#############################################################

–declare our date variables
DECLARE @LowerBoundDate DATETIME
DECLARE @UpperBoundDate DATETIME

–set the initial dates
SET @LowerBoundDate = ‘1/1/1980’;
SET @UpperBoundDate = ‘1/1/2040’;

–create our temporary table
DECLARE @TempDateDimension TABLE
(
        Date DATETIME
    ,    [Year] INT
    ,    [Month] INT
    ,    [Day]    INT
);

–use CTE to create our date iterator
WITH CalculatedDate AS
(
SELECT    
        @LowerBoundDate Date

UNION ALL

SELECT
        (Date + 1)
FROM
            CalculatedDate
WHERE
        ((Date + 1) <= @UpperBoundDate)
)

    –insert each date to our temporay dimension table
    INSERT INTO @TempDateDimension
    SELECT
            Date
        ,    Year(Date) AS [Year]
        ,    Month(Date) AS [Month]
        ,    Day(Date) AS Day
    FROM
        CalculatedDate

OPTION (MAXRECURSION 0)

–Insert the dates to our permanent Dimension table
INSERT INTO dbo.DateDimension
SELECT     
        Date
    ,    [Year]
    ,    [Month]
    ,    [Day]
FROM
    @TempDateDimension

–View the contents of the table
SELECT
        Date
    ,    [Year]
    ,    [Month]
    ,    [Day]
FROM
    DateDimension WITH(NOLOCK)


Sweet! It looks confusing at first but once you get use to it(and know whole nine yards about CTE) you’ll find how great CTE of a help can be to you in your SQL projects.


I hope this helps someone with a similar dilema that I had to day.


Get the scripts here: Date Dimension Table.zip | Date Dimension Table(using CTE).zip

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.