Simplify queries with a Dates table

Learn what a Dates table is and how to create one, and then try it out for yourself.

What is a Dates table?
A Dates table stores a range of dates. Dates tables are very common in a DateWarehouse as a dimension table. You can also use Dates tables in OLTP databases for lookups.

When programmers use Dates tables, they don’t have to worry about using or designing functions for handling or formatting dates in the database.

It is a precompilation of a wide range of date values and their associated month, quarter, year, etc.

Creating a Dates table
It's simple to create a Dates table--it only takes a little TSQL programming. The script below creates the DateLookup table, which I will use throughout the rest of the example.

CREATE TABLE DateLookup
 (
     DateKey INT PRIMARY KEY,
     DateFull DATETIME,
     CharacterDate VARCHAR(10),
     FullYear CHAR(4),
     QuarterNumber TINYINT,
     WeekNumber TINYINT,
     WeekDayName VARCHAR(10),
     MonthDay TINYINT,
     MonthName VARCHAR(12),
     YearDay SMALLINT,
     DateDefinition VARCHAR(30),
     WeekDay TINYINT,
     MonthNumber TINYINT
 )

As you can see from the field names, the table contains detailed information regarding parts of a date, such as the name of the month, name of the day of the weekend, the quarter number, etc.

It's very useful to have this information stored in a table for date searches based on certain months, quarters, and similar information.

The script below populates my DateLookup table with date information from the year 1900 through the end of 2100. I enter this large range of dates because I am not sure what type of dates I am going to handle in my tables, so I like to have a wide range available.

This range likely won't cover erroneous dates in my tables, but it should do a pretty good job covering a large percentage of them.

DECLARE @Date DATETIME
 SET @Date = '1/1/1900'     

 WHILE @Date < '1/1/2100'
 BEGIN
     INSERT INTO DateLookup
     (
         DateKey, DateFull, FullYear,
         QuarterNumber, WeekNumber, WeekDayName,
         MonthDay, MonthName, YearDay,
         DateDefinition,
                CharacterDate,
                WeekDay,
                MonthNumber
     )
     SELECT
         CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
         DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
         DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
           DATENAME(mm, @Date) + ' ' 
           + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',   
           ' + CAST(DATEPART(yy, @Date) AS CHAR(4)),
           CONVERT(VARCHAR(10), @Date, 101),
           DATEPART(dw, @Date),
           DATEPART(mm, @Date)
    
     SET @Date = DATEADD(dd, 1, @Date)
 END

Using the DateLookup table
Once I load data into my DateLookup table, I can run queries against it. For example, the following query lists the number of Wednesdays in the year 2003:

SELECT WeekDayName, DayCount = COUNT(*)
 FROM DateLookup
 WHERE FullYear = 2003 AND
 WeekDayName = 'Wednesday'
 GROUP BY WeekDayName

There were 52 Wednesdays in 2003.

The real power of using a Dates table comes when you use the table in conjunction with other tables. In the following example, I use my DateLookup table and join it to my SalesHistory table (from my article about generating dynamic SQL statements in SQL Server) on the SaleDate. Performing this join makes it easier for me to analyze my sales information based on the date the sale occurred.

The following query ranks the month with the highest sales per product line:

SELECT *
 FROM
 (
         SELECT                
                dd.MonthName,
                Product,
                RecordCount = COUNT(*),
                Ranking = DENSE_RANK() OVER ( PARTITION BY Product ORDER BY COUNT(*) DESC, NEWID() DESC )
         FROM   
                DateLookup dd
                JOIN SalesHistory s ON dd.DateKey = CONVERT(VARCHAR(8), SaleDate, 112)
         GROUP BY
                dd.MonthName,
                Product
 ) a
 WHERE Ranking = 1

This query ranks product sales per quarter per year:

SELECT 
         FullYear,
         QuarterNumber,
         Product,
         SaleCount = COUNT(*)
 FROM   
         DateLookup dd
         JOIN SalesHistory s ON dd.DateKey = CONVERT(VARCHAR(8), SaleDate, 112)
 GROUP BY
         FullYear,
         QuarterNumber,
         Product
 ORDER BY
         FullYear,
         QuarterNumber

Without the DateLookup table, this query would have to be written like this, which I think is more difficult:

SELECT 
         DATEPART(yy, SaleDate),
         DATEPART(qq, SaleDate),
         Product,
         SaleCount = COUNT(*)
 FROM   
         SalesHistory s
 GROUP BY
         DATEPART(yy, SaleDate),
         DATEPART(qq, SaleDate),
         Product
 ORDER BY
         DATEPART(yy, SaleDate),
         DATEPART(qq, SaleDate)

Drawback
The main drawback to using the Dates table is with the way in which I have to join it back to my OLTP tables. My Dates table does not contain time data, and my OLTP data almost certainly will; this means that joining the two tables requires some conversion on my part to do in the join. While this conversion in the join is not necessarily a huge deal, it may lead to poor performance under certain conditions.

Tim Chapman, a SQL Server database administrator and consultant who works for a bank, has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.