Brent Ozar: Date Table Fan

Mike Wolfe -

In an earlier article, I wrote about creating a monthly lookup table.  

Former Access MVP Tom Wickerath pointed me to a Brent Ozar video where he extols the virtues of a daily calendar table (thanks, Tom!).  Brent's a SQL Server guy, but his reasons for using a daily calendar table apply equally if you store your backend data in an Access file.  Obviously, the table creation process itself will differ.

In the video, Brent lists four reasons for creating a date lookup table:

  1. Replace DATENAME [(Format in Access)] with quick and easy lookups
  2. Establish a source for fiscal periods
  3. Create date slices so all days/periods are reported
  4. Standardize period names and stop writing redundant strings

Most of the above reasons fall into the category of convenience.  It's not particularly difficult to achieve the objectives of 1, 2, or 4 without a date table, but using the date table does make doing those things much easier.

The Killer Feature

The absolute killer feature of a date table is its ability to provide date placeholders when grouping date-based data that may have gaps in it.  Jump to the 15:00 minute mark of the video to see Brent's demonstration of that particular feature.  

For what it's worth, that feature is the main reason I first created a monthly lookup table in my own applications.

The Video

Why You Simply Must Have a Date Table

Cover image is a screen capture from Brent's video