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