Data Management

Simplify SQL Server 2005 queries with a Dates table

DBA Tim Chapman explains why he finds that it can be advantageous to use a Dates table, especially when doing a lot of date calculations.

I find that it can be advantageous to use a Dates table, especially when doing a lot of date calculations. 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 in Louisville, KY. Tim 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.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

15 comments
MattNYDC
MattNYDC

Just FYI if anyone is still references this extremely helpful page, I had a date lookup table administered by a full BI department at a previous company. I now run SQLExpress on a much smaller scale and this is exactly what i need (its very easy to overcome the 'shortcomings' of the script'. Just one note for anyone attempting to use this in SQL Server 2012, you'll receive a truncation error from the DateDefinition field. I didntt take the time to figure out the issue from the insert as i didnt need the column, but if you receive the error, that's where you should look. Thanks again!

Eric.Edberg
Eric.Edberg

The DatePart(ww,@Date) function also may misrepresent WeekNumber depending on your perspective. 1/1/2010 is a Friday and really the last "business week" of 2009 which starts on Sunday 12/27/2009 thru Sat 1/2/2010. You may also need to ignore the fact that business work weeks often start on Monday thru Sunday, but that's another local altogether... Other WorkWeek calculations can be local modifications to this tool.

shashankkale
shashankkale

Hi, Tim I tried your code in mysql but receive following Error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @Date DATETIME SET @Date = '1/1/1900' WHILE @Date < '1/1/2100' at line 1 Thank You...For Your Time

tariq
tariq

It is an excellent and very helpful article. Saves hours of work. Best wishes for you. Tariq Changgez

chapman.tim
chapman.tim

Thanks for noticing...silly bug on my part. I'll have it corrected.

sudars80
sudars80

Certainly a good technique though

nahkit_cj
nahkit_cj

I'm not familiar with SQL but my company is considering using it in the near future, hence my interest. The following code has got me scratching my head though: SELECT WeekDayName, DayCount = COUNT(*) FROM DateLookup WHERE FullYear = 2003 AND WeekDayName = 'Thursday' GROUP BY WeekDayName How does selecting WeekDayName of Thursday give you a count of all of the Wednesdays in 2003? Hopefully someone will put me out of my misery.

csnyman
csnyman

Very useful. I am busy setting up a data warehouse and a date dimension table was on my list of things to do. Thanks.

Tony Hopkinson
Tony Hopkinson

one. I used to use it for things like reporting production quantities over last shift, day, week etc. Slightly different derivatives for durations as well. It's especially handy with an outer join where you want to report 0s in a particular time range. Trying to do that in code, SP or application can soon have you doing some extremely inefficient stuff, like select everything that isn't there...

Guitarzan8
Guitarzan8

I like self maintaining code. What do you think about: put the sproc in a job, run once per year. delete from DateLookup SET @Date = '1/1/' + convert(char(4), datepart("yyyy",getdate)-100) and WHILE @Date < '1/1/' + convert(char(4),datepart(yy,getdate()) + 100)

tony.taylor
tony.taylor

Thanks for the code... For us Access Developers, here is some ADO/VBA... Function CreateDateTable() On Error GoTo CreateDateTable_Err Dim dt As Date Dim con As New ADODB.Connection Dim cmd As New ADODB.Command Dim rst As ADODB.Recordset Dim strSQL As String Dim tblNew As ADOX.Table Dim cat As New ADOX.Catalog Set con = Application.CurrentProject.Connection Set cmd.ActiveConnection = con Set cat.ActiveConnection = con Set tblNew = New ADOX.Table tblNew.Name = "tblDates" tblNew.Columns.Append "DateKey", adInteger tblNew.Columns.Append "DateFull", adDate tblNew.Columns.Append "CharacterDate", adVarWChar, 10 tblNew.Columns.Append "FullYear", adVarWChar, 4 tblNew.Columns.Append "QuarterNumber", adUnsignedTinyInt tblNew.Columns.Append "WeekNumber", adUnsignedTinyInt tblNew.Columns.Append "WeekDayName", adVarWChar, 10 tblNew.Columns.Append "MonthDay", adUnsignedTinyInt tblNew.Columns.Append "MonthName", adVarWChar, 12 tblNew.Columns.Append "YearDay", adInteger tblNew.Columns.Append "DateDefinition", adVarWChar, 30 tblNew.Columns.Append "WeekDay", adUnsignedTinyInt tblNew.Columns.Append "MonthNumber", adUnsignedTinyInt tblNew.Keys.Append "PrimaryKey", adKeyPrimary, "DateKey" cat.Tables.Append tblNew Set tblNew = Nothing Set rst = New ADODB.Recordset rst.Open "tblDates", con, adOpenStatic, adLockOptimistic, adCmdTable dt = #1/1/1900# Do While dt < #1/1/2100# rst.AddNew rst("DateKey") = Format(dt, "yyyymmdd") rst("DateFull") = dt rst("FullYear") = Year(dt) rst("QuarterNumber") = DatePart("q", dt) rst("WeekNumber") = DatePart("ww", dt) rst("WeekDayName") = WeekdayName(DatePart("w", dt), False, vbSunday) rst("MonthDay") = DatePart("d", dt) rst("MonthName") = MonthName(DatePart("m", dt), False) rst("YearDay") = DatePart("y", dt) rst("DateDefinition") = MonthName(DatePart("m", dt)) & " " & CStr(DatePart("d", dt)) & ", " & CStr(DatePart("yyyy", dt)) rst("CharacterDate") = CStr(dt) rst("WeekDay") = DatePart("w", dt) rst("MonthNumber") = DatePart("m", dt) rst.Update dt = dt + 1 Loop MsgBox "Creation complete." CreateDateTable_Exit: On Error Resume Next Set tblNew = Nothing rst.Close Set rst = Nothing Set con = Nothing Exit Function CreateDateTable_Err: MsgBox "Error in CreateDateTable()." Resume CreateDateTable_Exit End Function

eric
eric

I decided to give this a real world try and implemented it in a financial database software to look at trends. I compared it to my stored procedures that I usually run and found that in general that this table approach was actually significantly faster. So instead of figuring out why my SP are slower, I think I might just stick with this "quick" and dirty solution. Great Job! Anybody else try this out on a real set of tables? Any comparison against your SP? -Eric

Quicklynx
Quicklynx

I was running through the code, so I could understand it better. I think this will help me with a DB I am working on. When I ran the query for Wednesdays, it returned 53 instead of 52 ( The number for Thursdays ). Not sure it is a big deal, but I just in case you want to cross your t's and dot your i's.

Tony Hopkinson
Tony Hopkinson

That was the traditional stupid coding error so QA can gain job satisfaction from finding a bug. :D I thing you may end up with a count of all thursdays from that particular query.

wwest
wwest

create a table of numbers (mine is called zNumbers, containing one field called Number type int), load it with a loop with the range 0 - 80,000. You can then load the above table with a single insert statement that runs in 3 seconds on my system: INSERT INTO DateLookup ( DateKey, DateFull, FullYear, QuarterNumber, WeekNumber, WeekDayName, MonthDay, MonthName, YearDay, DateDefinition, CharacterDate, WeekDay, MonthNumber) SELECT CONVERT(VARCHAR(8), cast(Number as datetime), 112), cast(Number as datetime), YEAR(cast(Number as datetime)), DATEPART(qq, cast(Number as datetime)), DATEPART(ww, cast(Number as datetime)), DATENAME(dw, cast(Number as datetime)), DATEPART(dd, cast(Number as datetime)), DATENAME(mm, cast(Number as datetime)), DATEPART(dy, cast(Number as datetime)), DATENAME(mm, cast(Number as datetime)) + ' ' + rtrim(CAST(DATEPART(dd, cast(Number as datetime)) AS CHAR(2))) + ', ' + CAST(DATEPART(yy, cast(Number as datetime)) AS CHAR(4)), CONVERT(VARCHAR(10), cast(Number as datetime), 101), DATEPART(dw, cast(Number as datetime)), DATEPART(mm, cast(Number as datetime)) from zNumbers (I just hope this formats and displays reasonably!) I used a similar technique when I needed a function for calculating the days between two dates but not counting weekends and holidays. A numbers table built that out beautifully.

Editor's Picks