As a developer, one of the things I can no longer live without is a date table. Who wants to type DATENAME and DATEPART over and over again? Not this guy, and once you have a date table, you won’t want to either.
In this 16-minute video, I’ll give you four reasons why you should stop writing so many date functions and concatenations, and start looking them up in a table instead.
24 Comments. Leave new
Awesome example. Do you plan to share TSQL that populate the date table?
Mark V – here’s the one I usually use: http://www.made2mentor.com/2011/06/creating-a-date-tabledimension-for-sql-server-2008/
Thank you!
Thanks for the link, Master O.
I love my date table! I find that I add to it as I need more columns. Great presentation!
Thanks, Andrea!
Great stuff. Like the fireplace gag; love the date table.
Looking forward to the Tuesday webcast. Thanks!
You’re welcome, Mike. Glad you liked it.
Would you suggest having a central date table to share across databases? Perhaps referenced in the other databases through a view? Thinking that way if some specific application needed something slightly different it would be easier to refactor.
Yeah, I like having it either in a utility database or in master. You can refer to it through views in other databases and secure those as needed.
Using this already. Need to join against non existing dates to make a clean chart of yearly stats.
Damn! Why didn’t I think of that! 😉 Great post! Thanks. Implementing now!
I’ll be using querying lots against a Year, Week and DayOfWeek fields, should I create an index on each those columns to speed up my joins?
In the examples you provided for linking in the date as an INT, you had the luxury of storing it as an int in the foreign table as well.
What makes this more difficult is if you have a table that had multiple dates in it (maybe a created date, an updated date, an outcome date, etc). Now you have to have 3 additional fields for each so that you have an INT.
A previous developer at our company was instead casting them as an int during the join, but I think that was really inefficient.. For example….
SELECT * FROM A JOIN DimDate B on B.DateKey = Cast(A.CreateDate as Int)
actually in many cases they were joining on the key which was a date, and casting as a date so they could get rid of time. As you showed in your video, joining on the date is less efficient that joining on the int.
I also believe you mentioned using a computed column. Unless you persist that, it’s being calculated every time it’s access. So is that any worse than casting it on the join?
Hoping you could speak to the computed column, or how to modify existing tables to support the INT joining.
Hi Duncan,
What you’re describing is a classic role-playing dimension in a cube model (like SQL Server Analysis Services might have). You end up joining to the date table three times if you have three different dates. It’s a little tedious to set up but not as inefficient as it may sound.
You’re right about persisting the computed column. The good news is that if you had to do it that way, it’d still torch the CAST() in the join performance-wise. Joining on a date is okay; it depends on what’s easier with your existing data model.
To answer your last question, if you don’t have integer date keys in your environment already, you’re probably better served joining to dates than you are adding ints everywhere in your other tables. Only if performance is observably poor would I start adding date conversions to the source tables.
Another really useful column to add is “Same Day Last Year.” This allows you to do year over year comparisons without having to figure out what the correct comparison day is. It also allows you to adjust certain days, like Easter, so that you’re comparing this year’s Easter with Last Year’s Easter.
Yes indeed! I can see how a retailer would want to know things like the date of Black Friday (in the US, anyway) from year to year.
Could you please write all this down somewhere (like in a blog post maybe)? Talking heads are cool and all, but they make terrible references, and Google can’t search video at all.
Hi Steve,
I agree it’d be nice to have text to refer to both for reading and for SEO. Unfortunately, the webcasts take long enough to make that there just isn’t time for transcriptions too. Thanks for your comment!
We love helping folks out with free information and we’ve looked into quality transcription services before, but it’s a big investment and more complicated than it seems: verbatim transcriptions really just aren’t that readable! We’re always on the lookout for good blog post ideas, though, so maybe more posts on this topic will come your way.
Kendra
Agree, Videos are great, if you can watch them. I currently work at a company where Youtube is blocked and it’s not the first time I’ve worked for an organization that did so.
Dunno if you’re aware, Brent, but the video is no long available.
Doh! Sorry about that, fixed.
Serious question. It’s 2023. This is still a good idea right? We don’t just load all the data into Fabric and let Aaron Neville’s magical singing make the dates all work out.
That’s a really good question, actually. I’m a huge fan of the modern data warehouse approach, but… not quite a fan of Fabric yet.