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?
[…] 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. […]
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.
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?
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′.
The title of this field, DateFormatYYYYMD is inconsistent with the data which has values such as 2009/15/1 and 2009/31/1.