Archives

Creating a Date Table/Dimension on SQL 2008.

In a previous article, I listed the benefits of using a dedicated date table and included a customizable script which enables you to quickly create your own version. One of my readers pointed out that he uses the date datatype, rather than using the smart integer key method, when working with SQL 2008+ databases. The smart date key is recommended by the Kimball Group and others.

I performed several tests comparing the performance of Date vs. Integer datatype joins and found the Date joins to perform faster as well.

As a result, I’ve modified my script to create a Date datatype dimension have begun using it. As before, it is in two parts. The first script can be found here. Open the script, replace DateDatabase with the database name of your choice, and run it to create the function.

Once again, I need to state that I created this script from a function called F_TABLE_DATE by Michael Valentine Jones from SQLTeam and have since gotten his permission to distribute it. That function is called in the statement below to create the date table/dimension.

IF EXISTS
     (SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'DimDate')
  BEGIN
        DROP TABLE dbo.DimDate;
  END
 
SELECT DTE.Date,
       DTE.NextDayDate,
       DTE.CalendarYear,
       DTE.CalendarYearQuarter,
       DTE.CalendarYearMonth,
       DTE.CalendarYearDayOfYear,
       DTE.CalendarQuarter,
       DTE.CalendarMonth,
       DTE.CalendarDayOfYear,
       DTE.CalendarDayOfMonth,
       DTE.CalendarDayOfWeek,
       DTE.CalendarYearName,
       DTE.CalendarYearQuarterName,
       DTE.CalendarYearMonthName,
       DTE.CalendarYearMonthNameLong,
       DTE.CalendarQuarterName,
       DTE.CalendarMonthName,
       DTE.CalendarMonthNameLong,
       DTE.WeekdayName,
       DTE.WeekdayNameLong,
       DTE.CalendarStartOfYearDate,
       DTE.CalendarEndOfYearDate,
       DTE.CalendarStartOfQuarterDate,
       DTE.CalendarEndOfQuarterDate,
       DTE.CalendarStartOfMonthDate,
       DTE.CalendarEndOfMonthDate,
       DTE.QuarterSeqNo,
       DTE.MonthSeqNo,
       DTE.FiscalYearName,
       DTE.FiscalYearPeriod,
       DTE.FiscalYearDayOfYear,
       DTE.FiscalYearWeekName,
       DTE.FiscalSemester,
       DTE.FiscalQuarter,
       DTE.FiscalPeriod,
       DTE.FiscalDayOfYear,
       DTE.FiscalDayOfPeriod,
       DTE.FiscalWeekName,
       DTE.FiscalStartOfYearDate,
       DTE.FiscalEndOfYearDate,
       DTE.FiscalStartOfPeriodDate,
       DTE.FiscalEndOfPeriodDate,
       DTE.ISODate,
       DTE.ISOYearWeekNo,
       DTE.ISOWeekNo,
       DTE.ISODayOfWeek,
       DTE.ISOYearWeekName,
       DTE.ISOYearWeekDayOfWeekName,
       DTE.DateFormatYYYYMMDD,
       DTE.DateFormatYYYYMD,
       DTE.DateFormatMMDDYEAR,
       DTE.DateFormatMDYEAR,
       DTE.DateFormatMMMDYYYY,
       DTE.DateFormatMMMMMMMMMDYYYY,
       DTE.DateFormatMMDDYY,
       DTE.DateFormatMDYY,
       DTE.WorkDay,
       DTE.IsWorkDay
       into dbo.DimDate
 From dbo.F_TABLE_DATE ( '20000101','20351231' ) DTE
 order by 1
 Go
 
ALTER TABLE dbo.DimDate
ADD  PRIMARY KEY CLUSTERED ([Date])
WITH FILLFACTOR = 100
GO
 
CREATE INDEX idx_Dates
ON dbo.DimDate 
	(NextDayDate)
GO

My example above generates every day through 2035, but nearly any date range can be used. As before, if you don’t want to have such a comprehensive Date Dimension or there are fields you don’t need, simply comment out items from the Select into query.

Querying against the Date Dimension is done the same way as with my previous script.

SELECT
  MY.date
 ,DTE.CalendarYear
 ,DTE.CalendarMonthName
FROM
    MyTable MY
  INNER JOIN
    DateDatabase.dbo.DimDate DTE
  ON MY.date >= DTE.date AND
     MY.date < DTE.NextDayDate

Any questions or suggestions for improvement?

Related posts:

5 comments to Creating a Date Table/Dimension for SQL Server 2008.

  • [...] Calendar Tables – Why You Need One. *** Note: The code below is for use with SQL 2005 and previous versions. I have written new code to take advantage of the Date Datatype in SQL 2008 and above which can be found here. [...]

  • nanobi

    Thanks for the post. It was a tremendous help.

    I noticed that I can’t use any dates prior to 1900/01/02 because your first script inserts 19000101 as an indexed field into @Date. This part of your script isn’t present in the one provided by Michael Jones. [Ln 690 - 704] Is there a reason why this row is inserted into the table? Some of the fields are listed as TBD or NA and the CalendarYearDayOfYear has an extra 0.

    To use the function dbo.F_TABLE_DATE with a date range that includes 1900/01/01, I commented out Ln 702 – 704. This removed the duplicate primary key issue.

    I am very grateful to you and Michael for taking the time to put this together with detailed explanations that a novice, such as myself, can easily understand.

  • The reason I did that is because ’1/1/1900′ is because many use that date as their “Beginning of Time” value. Or, they use it as the value they substitute when there is no date.

    It’s similar to the ‘-1′ record you will see inserted into Dimensions. If there is no match to a Dimension Record during a Fact Table Load, people will often replace the NULL with ‘-1′ and that record has values in the Dimension like ‘Unknown’ or ‘N/A’ so referential integrity can be maintained.

    You are correct that if you have to enter dates into your Date Dimension prior to ’1/1/1900′ then you’ll have to edit the posted code. Perhaps I could preface that insert statement with a check to see if ’1/1/1900′ was already in the table. I’ll check into making that change in the near future. Thanks for the comment.

  • Chris

    Why have you included a Fiscal section without the ability to easily offset it for people who decide to do silly things like start the Fiscal Year on Oct 1?

  • Parasuraj

    This challenge is to find the date based on year, month, day of the week and weekday number. Say for example, if the question is to find the date of 2nd Sunday of January 2010, the answer should be ’2010-01-10′.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>