Overview

Date tables can greatly simplify the development and maintainance of date-related SQL. Combining this technique with a recursive query and the built-in date functions offers a lot of functionality from a small amount of code. In this article I’ll cover two types of date tables, address some design decisions, and show some sample queries.

The Calendar Table

The first table is a variation of the common date table. Simple in concept, it contains one row for each date and all the useful date attributes – year, quarter, month, week of year, day of week, etc. It should be tailored to your environment and can contain additional columns such as fiscal year and fiscal period. You will probably end up extending it over time as you solve specific problems. For example, I added the first and last day of the month to each row for my current client to improve a slow query.

There are several options for implementing your date tables. One option is to run a daily process to repopulate the table. This is useful if you want a table that ends on the current date, or has values that change based on the current date. Another option is to define a view. This is convenient because no update process is required – the view is always current. The tradeoff is the view must be materialized when queried, but in my experience this usually takes only milliseconds.

Here is an example of a date table named Calendar, implemented as a view defined by a recursive query.

create view [dbo].[Calendar]
as
with Dates as (
   select cast('2010-01-01' as date) as CalendarDate
   union all
   select dateadd(day , 1, CalendarDate) AS CalendarDate
   from Dates
   where dateadd (day, 1, CalendarDate)<= dateadd(year, 1, getdate())
)
select
   CalendarDate,
   CalendarYear=year(CalendarDate),
   CalendarQuarter=datename(quarter, CalendarDate),
   CalendarMonth=month(CalendarDate),
   CalendarWeek=datepart(wk, CalendarDate),
   CalendarDay=day(CalendarDate),
   CalendarMonthName=datename(month, CalendarDate),
   CalendarDayOfYear=datename(dayofyear, CalendarDate),
   Weekday=datename(weekday, CalendarDate),
   DayOfWeek=datepart(weekday, CalendarDate)
from Dates

In this example the view contains all dates from January 1, 2010 to the current date. You will want to customize the date range to fit your environment. Limiting the range to the dates possible for your data helps keep the query efficient but you want a continous range to keep your options open when writing queries. If you opt to use a table instead you can extract this query to populate it. I sometimes embed a similar query in a SSIS package to populate a time dimension for BI projects. You can also parameterize this query in a stored procedure to generate a specific range of dates.

If your definition of week does not begin on the default day you can easily customize date function behavior using DATEFIRST. For example, this statement sets Sunday as the first day of the week:

SET DATEFIRST 7

The parameter represents the first day of the week, defined as follows:

1. 1 Monday
2. 2 Tuesday
3. 3 Wednesday
4. 4 Thursday
5. 5 Friday
6. 6 Saturday
7. 7 Sunday

With this table defined most date logic is easy to implement and fairly self-documenting. A recent question was how to determine the first Tuesday 10 days after a given date. Using the table, you can write this query:

with Orders as(
   select OrderDate=cast('2011-01-01' as date)
)
select OrderDate,
   FirstTuesday=(
      select top 1 CalendarDate
       from Calendar
       where CalendarDate >= dateadd(day, 10, o.OrderDate)
       and Weekday = 'Tuesday'
      order by CalendarDate
      )
from Orders o
option (maxrecursion 1000)

Here I use a common table expression (CTE) to define one test row. The second select statement queries the test row and uses a correlated subquery to find the correct Tuesday. Note the maxrecursion option in the last line. This is necessary because of the recursive query in the Calendar view. By default a recursive query can only recurse 100 times before execution is terminated. I’ve arbitrarily specified 1000 recursions so this query will run to completion. In production code you will probably want to choose a value carefully to help catch bugs. You can avoid needing the option statement by populating a table instead of using the view.

Join logic is simple when using the date type. If you are working with datetime values update your logic to handle the time portion. You can use logic like this:

join Calendar c on MyDateTime >= c.CalendarDate 
and MyDateTime < dateadd(day, 1, c.CalendarDate)

The CalendarPeriod Table

The second table is the CalendarPeriod table. CalendarPeriod makes it easy to select a range of dates based on a time period relative to the current date. I often use this with reports to give the user a simple set of options such as 1D, 7D, 1W, 2W, 1M (one day, seven days, one week, two weeks, one month). In my terminology 7D would be the current date and the six days prior, where 1W would be aligned by the first day of the week. You can define any periods that are of interest, including week-to-date, month-to-date, year-to-date, etc.

Here is the CalendarPeriod definition, also as a view:

create view CalendarPeriod
as
select CalendarPeriod='All', CalendarDate=cast(null as date)
union all
select CalendarPeriod='1D', CalendarDate=cast(getdate() as date)
union all
select CalendarPeriod='2D', CalendarDate
from Calendar
where CalendarDate between dateadd(day, -1, cast(getdate() as date)) and cast(getdate() as date)
union all
select CalendarPeriod='7D', CalendarDate
from Calendar
where CalendarDate between dateadd(day, -6, cast(getdate() as date)) and cast(getdate() as date)
union all
select CalendarPeriod='14D', CalendarDate
from Calendar
where CalendarDate between dateadd(day, -13, cast(getdate() as date)) and cast(getdate() as date)
union all
select CalendarPeriod='21D', CalendarDate
from Calendar
where CalendarDate between dateadd(day, -20, cast(getdate() as date)) and cast(getdate() as date)
union all
select CalendarPeriod='1W', CalendarDate
from Calendar
where CalendarYear = year(getdate()) and CalendarWeek = datepart(wk, getdate())
union all
select CalendarPeriod='2W', CalendarDate
from Calendar
where (CalendarYear = year(getdate()) and CalendarWeek = datepart(wk, getdate()))
or (CalendarYear = year(dateadd(week, -1, getdate())) and CalendarWeek = datepart(wk, dateadd(week, -1, getdate())))
union all
select CalendarPeriod='1M', CalendarDate
from Calendar
where CalendarYear = year(getdate()) and CalendarMonth = month(getdate())

Here is an example that queries the last seven days of snapshot using a simple join to CalendarPeriod.

with Snapshots as (
select OrderDate=cast(GETDATE() as date), OrderAmount=123.0 union all
select OrderDate=cast(dateadd(day, -1, GETDATE()) as date), OrderAmount=124.0 union all
select OrderDate=cast(dateadd(day, -2, GETDATE()) as date), OrderAmount=125.0 union all
select OrderDate=cast(dateadd(day, -3, GETDATE()) as date), OrderAmount=126.0 union all
select OrderDate=cast(dateadd(day, -4, GETDATE()) as date), OrderAmount=127.0 union all
select OrderDate=cast(dateadd(day, -5, GETDATE()) as date), OrderAmount=128.0 union all
select OrderDate=cast(dateadd(day, -6, GETDATE()) as date), OrderAmount=129.0 union all
select OrderDate=cast(dateadd(day, -7, GETDATE()) as date), OrderAmount=130.0 union all
select OrderDate=cast(dateadd(day, -8, GETDATE()) as date), OrderAmount=131.0 union all
select OrderDate=cast(dateadd(day, -9, GETDATE()) as date), OrderAmount=132.0 union all
select OrderDate=cast(dateadd(day, -10, GETDATE()) as date), OrderAmount=133.0 union all
select OrderDate=cast(dateadd(day, -11, GETDATE()) as date), OrderAmount=134.0 union all
select OrderDate=cast(dateadd(day, -12, GETDATE()) as date), OrderAmount=135.0 union all
select OrderDate=cast(dateadd(day, -13, GETDATE()) as date), OrderAmount=136.0
)
select OrderDate, OrderAmount
from Snapshots
join CalendarPeriod cp on cp.CalendarDate = OrderDate and CalendarPeriod = '7D'
option (maxrecursion 1000)

If you use this technique at some point you may want to include an “All” period. Rather than including a row for every date I use one row with a null CalendarDate in the view definition and add special join logic like this:

join CalendarPeriod cp on (cp.CalendarDate = OrderDate and CalendarPeriod = @Period)  or (@Period = ‘All’)

An advantage of this table is your date range logic is consolidated. You can query the distinct set of a CalendarPeriods and present it as a parameter in reports. You also don’t have to include logic like between dateadd(day, -6, cast(getdate() as date)) and cast(getdate() as date) throughout your code which facilitates reuse and can help prevent bugs.

This technique has served me well on many projects. I hope you find it useful.