*** 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.
What is a Date (Calendar) Table?
For the purposes of this article a Date Table is a dedicated table containing a single record for each day in a defined range. They include fields with descriptive attributes for each day such as Year, Month, Week, and whether a particular day is a work day or not. Date Tables (Dimensions) are an integral part of every Data Warehouse, but they can be used with a regular (OLTP) database as well.
Why do I need a Date Table?
There are several situations in which a date table is extremely helpful. Let’s consider just a few. Suppose you need to….
- Generate a running sales total per day and include days in which you have no sales such as weekends.
- Calculate the number of workdays between two dates.
- Calculate projected ship dates for products based upon lead times.
- Aggregate data by non-standard Fiscal Years or Periods. Perhaps your company uses the Fiscal Year of the US Government which runs from October 1st until September 30th. My current employer uses the 4-4-5 Calendar methodology, which is what originally led me to create this Date Table script.
- Need to track data by seasons, whatever that means to your company. For example, if you manufactured bicycles you’d certainly want to compare sales figures in the spring versus fall.
- Need to compare data using rolling months/periods. Suppose your company wants to know how March sales for this year compared to March for the past 5 years. Or you want to compare sales which occurred on Fridays to each other to look for trends.
- Need to create Gantt Charts.
While many of these issues can be handled by writing T-SQL code, they’re much easier with a dedicated date table. Also, the Date Table can be customized to track anything about a particular day you want.
What about Space Considerations?
One concern that people have when creating a Date Table is the “wasted” space since each day is its own record. The space used is negligible, especially when compared to the benefits of having a dedicated date table. For example, when I execute my script for the years 2000 through 20035 the space used is less than 250MB.
How do I create a Date Table?
Kimball Date Dimensions
In the The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2and the previous 2005 version, the Kimball Group recommends that you create a Date Dimension using Excel. In the latest book, they provide a Date Dimension Spreadsheet which is available for Download and utilizes VBA to generate the table. They recommend you create a SSIS package to load that data into SQL Server.
While this will work, and some non-technical people might prefer this method, I prefer to use SQL Script, because I may not always have access to SSIS.
Using a SQL Script
When I searched for a Date Table Script a few years ago, I couldn’t find something suitable for my needs. Most of them include only one set of fields (regular Calendar) and I to report via custom fiscal calendars.
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. I’ve modified the script so that it generates a Kimball style date dimension and it’s very comprehensive, yet simple to use and customize.
- Create a database called DateDatabase or whatever you’d like to call it. We’ll install F_TABLE_DATE in it and presumably the Date Table as well.
- Open the following script and replace “DateDatabase” (Find and Replace) with whatever database name you chose in step 1.
- Run the Script to create the function.
Your database should now contain the function used to create Date Tables. As a test, run the following script:
SELECT * FROM dbo.F_TABLE_DATE ('20000101', '20101231') ORDER BY 1
You should receive 4019 Rows, 11 years worth of days plus a record for unknown dates. Notice the extensive list of fields built into the function. All of them are documented in the script.
Use a Select Into statement to create the Date Table like so. Replace dbo.DimDate with whatever you’d like to call your Date Table.
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'DimDate') BEGIN DROP TABLE dbo.DimDate; END Select DateId ,Date ,NextDayDate ,CalendarYear ,CalendarYearQuarter ,CalendarYearMonth ,CalendarYearDayOfYear ,CalendarQuarter ,CalendarMonth ,CalendarDayOfYear ,CalendarDayOfMonth ,CalendarDayOfWeek ,CalendarYearName ,CalendarYearQuarterName ,CalendarYearMonthName ,CalendarYearMonthNameLong ,CalendarQuarterName ,CalendarMonthName ,CalendarMonthNameLong ,WeekdayName ,WeekdayNameLong ,CalendarStartOfYearDate ,CalendarEndOfYearDate ,CalendarStartOfQuarterDate ,CalendarEndOfQuarterDate ,CalendarStartOfMonthDate ,CalendarEndOfMonthDate ,QuarterSeqNo ,MonthSeqNo ,FiscalYearName ,FiscalYearPeriod ,FiscalYearDayOfYear ,FiscalYearWeekName ,FiscalSemester ,FiscalQuarter ,FiscalPeriod ,FiscalDayOfYear ,FiscalDayOfPeriod ,FiscalWeekName ,FiscalStartOfYearDate ,FiscalEndOfYearDate ,FiscalStartOfPeriodDate ,FiscalEndOfPeriodDate ,ISODate ,ISOYearWeekNo ,ISOWeekNo ,ISODayOfWeek ,ISOYearWeekName ,ISOYearWeekDayOfWeekName ,DateFormatYYYYMMDD ,DateFormatYYYYMD ,DateFormatMMDDYEAR ,DateFormatMDYEAR ,DateFormatMMMDYYYY ,DateFormatMMMMMMMMMDYYYY ,DateFormatMMDDYY ,DateFormatMDYY ,WorkDay ,IsWorkDay into dbo.DimDate -- Choose whatever name you like for your table. From dbo.F_TABLE_DATE('20000101','20251231') -- Edit the parameters for a custom date range. Order By 1
Don’t panic, you don’t need to use all of the fields which is why this script is so highly customizable. Any fields you don’t want can be commented out before running the Select Into statement, which allows you to create your own custom Date Table.
The final step is to add a Primary Key to the DateID and an index for the Date and NextDayDate fields. As I can’t predict the usage patterns for your date table, you’ll have to implement further indexing if necessary.
ALTER TABLE [dbo].[DimDate] ADD PRIMARY KEY CLUSTERED ([DateId]) WITH FILLFACTOR = 100 ON [PRIMARY] GO CREATE INDEX id_Dates ON DateDatabase.dbo.DimDate (Date, NextDayDate)
How do I use the Date Table?
Using the date table is simple, you simply link to it from the query from your source system. Use the following template.
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
Both DTE.Date and DTE.NextDayDate represent midnight of the two consecutive days. Therefore the time portion of a Datetime field can be ignored.
What about the fiscal fields?
After running these scripts, and examining the Date Table, you’ll notice that the fiscal fields mostly mirror the calendar fields. This is by design. In the next article I’ll explain how to customize the fiscal fields and a script to extract the fiscal information out of Made2Manage. Even if you don’t use M2M, you can still use the fiscal script as an example of how to do it.