Patterns and Practices: Creating a Date Table

There may be a time when you will need to create a table containing a series of dates. Perhaps you want a date table for a data warehouse or a data model in Excel’s Power Pivot. There are a number of ways you can create a date table in SQL Server. I will show a method, which we recommend at Pragmatic Works, that uses a Common Table Expression [CTE].

What fields do I need?

The fields needed in a date table generally start with a standard set of values listed here. Common values include things like an actual date, year, months listed by their full names, days of the week and quarters.  These values are included within the date table to provide the ability to both aggregate and display values for various time periods. 

• DateKey – Integer key representing the date, for example, 20150524
• CalendarDate – Date time field
• CalendarYear – Four digit year
• QuarterNumber – Number from 1  to 4
• QuarterName – First Quarter, Second Quarter, 1 Qtr 2015, First Quarter 2015 are some of the ways the name is listed. Pick whichever format people would like to see.
• MonthNumber – 1 for January, 2 for February, 3 for March etc.
• NameMonth –   January, February, March etc.
• MonthDayNumber – July 15 would have the value of 15 here
• WeekDayNumber  –  Date of the Month. July 4 would have a 4 here.
• CalendarWeekofMonth – Counts the weeks within each month
• NameDay – Monday, Tuesday etc.
• DayNumber –  Whether you choose 1 for Sunday or 1 for Monday is a business rule you need to find out first.  This example shows 1 for Sunday, 2 for Monday etc.
• YearDay – Sometimes referred to as the Julian number this counts the days from 1- 365 and can be useful in some calculations
• YearWeek – Counts the weeks from 1 -52

As a best practice, remember reserved words such as DATE or Index or any other word which shows up as blue or pink in SQL Server Management Studio, should never be the names of any columns. Always select a name other than a reserved word when creating tables. Also, friendly names containing spaces are great for exposing to users, but they are annoying in SQL Server. In this case, leave the spaces out of the column names. It is important to consider the range of dates needed in the table so that you don’t have to go back and add more values to the table because you included a date range without the appropriate number of years. Here the provided range of values is quite small, but that was done as an example, not to indicate the range of dates actually required. Review the dates stored within your data to determine the starting dates, and set the end dates for probably about five years longer than you think the data will still be in use.

Using a Recursive CTE

Microsoft added CTEs in 2005 and having a CTE call itself will create a recursive or self-referencing CTE.  Having a CTE repeatedly call itself and return data until the results are completed, is a very efficient way to generate a date table. This method harnesses the power of your computer to spin through your code, which decreases the need to write a lot of code. I am using a self-Referencing CTE method here first published by Itzik Ben-Gan to generate the number table being used in the code below.

CREATE TABLE [dbo].[DimDate](

[DateKey] int NULL,

[CalendarDate] [smalldatetime] NULL,

[CalendarYear] [int] NULL,

[QuarterNumber] [int] NULL,

[QuarterName] [varchar](14) NULL,

[MonthNumber] [int] NULL,

[NameMonth] [nvarchar](30) NULL,

[MonthDayNumber] [int] NULL,

[CalendarWeekOfMonth] [int] NULL,

[NameDay] [nvarchar](30) NULL,

[DayNumber] [int] NULL,

[YearDay] [int] NULL,

[YearWeek] [int] NULL

) ON [PRIMARY]

GO

/*Make sure you change the start and end dates listed here to the dates you wish to use in your table*/

DECLARE @StartDate smalldatetime = '01/01/2014'

DECLARE @EndDate smalldatetime = '12/31/2016' ; /* don't forget the semi-colon or you will get an error*/

/*This CTE is used to create a list of numbers used to generate the calendar*/

WITH

A00(N) AS (SELECT 1 UNION ALL SELECT 1),

A02(N) AS (SELECT 1 FROM A00 a, A00 b),

A04(N) AS (SELECT 1 FROM A02 a, A02 b),

A08(N) AS (SELECT 1 FROM A04 a, A04 b),

A16(N) AS (SELECT 1 FROM A08 a, A08 b),

A32(N) AS (SELECT 1 FROM A16 a, A16 b),

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM A32),

/*Calendar dates are created here*/

CalendarBase as (

SELECT

DateKey = n

, CalendarDate = DATEADD(day, n - 1, @StartDate )

FROM cteTally

WHERE N <= DATEDIFF(day, @StartDate , @EndDate +1)

)

/*Using the list of dates created above will populate your date table here*/

insert into dbo.DimDate(DateKey

,CalendarDate

,CalendarYear

,QuarterNumber

,QuarterName

,MonthNumber

,NameMonth

,MonthDayNumber

,CalendarWeekOfMonth

,NameDay1

,DayNumber

,YearDay

,YearWeek) 

SELECT

DateKey       = CONVERT(char(8), CalendarDate, 112)

, CalendarDate

, CalendarYear  = YEAR(CalendarDate)

, QuarterNumber =  (DATEPART(QUARTER,CalendarDate) )

, QuarterName = 'Quarter ' + cast((DATEPART(QUARTER,CalendarDate) ) as char(1)) +' ' + cast(YEAR(CalendarDate) as char(4))

, MonthNumber = MONTH(CalendarDate)

, NameMonth     = DATENAME(Month, CalendarDate)

, WeekDayNumber   = DATEPART(DAY, CalendarDate)

, CalendarWeekOfMonth = DATEDIFF(week, DATEADD(day,1, CalendarDate - DAY(CalendarDate) + 1) -1, CalendarDate) +1

, NameDay       = DATENAME (Weekday,CalendarDate )

, DayNumber       = DATEPART(Dw, CalendarDate)

, YearDay       = DATEPART(DAYOFYEAR, CalendarDate)

, YearWeek      = DATEPART(WEEK, CalendarDate)

FROM CalendarBase 

After running this code, you will have a date table created and loaded.

Why Do I need a Date Table?

There are a number of instances why adding a date table could be useful.  Date tables provide the ability to aggregate data based upon various segments of time rather than on the individual date the event occurred. For this reason, including a date table as part of the dimensional model for a data warehouse is considered a best practice. If you are interested in knowing how many times an event occurred within a given month or a given year, a date table can provide the means to determine the answer. If you are creating a data model in either the SQL Server Analysis Services Tabular Model or Power Pivot, a date table must be defined in order to be able to perform any sort of time calculation, such as parallel period or Total YTD. These are some of the most common reason for needing a date table, but whatever the reason, this code will allow you to create one quickly and efficiently.