THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

T-SQL Tuesday #001 (Date/Time Tricks): The Roundup

Wow! The response to the first T-SQL Tuesday was truly amazing. We ended up with 20 great posts, from all over the world. If you didn’t participate this time, fear not—we’ll be doing this every month from now on so there is plenty of time to jump in. And don’t forget our Twitter hashtag, #TSQL2sDay, which you can follow to keep up with T-SQL Tuesday even when it’s not the 2nd Tuesday of a month.

Here are the posts for this month, in the order in which trackbacks/comments were received on the invitation blog post:


daveb_bigger Kicking things off was Dave Ballantyne, who shared “Age calculation with SQL Server”. If you’ve ever tried to calculate someone’s age and run into leap year issues, read this post.


batfinkRob Farley shared a post entitled “A date dimension table with computed columns”. Rob’s offering should help those of you who work with date dimensions in Analysis Services.



Next up was Mike Walsh, with an intro post called “Dates and Time”. Mike covers many of the basics such as ISO date formats and whether you should cluster a date column. Definitely a good refresher!


avatar.aspx_thumb_2ED1E6ADBluedog67’s “Introduction to Effective Dating” has nothing to do with getting a girl’s phone number. Instead, he discusses the ins an outs of “effective dates”—in other words, the date that a piece of data became reality. A very interesting post!



Alan Wood brings us “Return a range of dates between 2 days”, a post in which he describes a function that does exactly what the title says it will. I often use calendar tables for this, but there are definitely times that a function makes more sense. Good stuff.


summitcloud_logo1_bigger.gif SummitCloud was not happy with some of the built-in SSRS features for Gantt charts, and posted an elaborate workaround in “Project Time Reporting Hack in SSRS”. A must-read if you need to create pretty reports based on time intervals.



Brad Schulz is feeling rather paranoid… Various dates are giving him night sweats and other forms of panic. In “Friday the 13th 2009: Cluster of Terror” he uses a calendar table an T-SQL to evaluate one popular day of doom. Scary!


me_bigger Speaking of scary, next up is the Scary DBA himself, Grant Fritchey, with a cautionary tale—how not to do date and time queries. Read Grant’s post and then check your code base. Are you doing something that could cause problems?



Jack Corbett is not sure whether he’s a wise man or a wise guy, but he does know how to retrieve date rages. He shares his insights in his example-filled T-SQL Tuesday post.



avatar.aspxAndy Leonard loves SSIS more than any man should love a computer program, and it shows. In “A Couple SSIS Date Expressions” he reveals the true nature of how to manipulate dates in our favorite Microsoft ETL program.



Mladen Prajdic thought that there was already plenty of date/time information out there—why reinvent the wheel? He shares a large number of links to previously-written posts in “SQL Server Date and Time fun from all around”.



kalen_d Ever the internals geek, Kalen Delaney uses her knowledge to answer a number of pressing date and time-related questions in “My Datetime FAQ”. If you’ve ever wondered about the significance of the year 1753, read this post.



Sean and Jen McCown are the Midnight DBA team, a couple that apparently prefers work to sleep. In “Remix! Optimized: Query by Hour, Day, Week, or Month” Jen shares the secrets of querying your data using a variety of granularities. Cool!



Jamie Thompson may be the SSIS Junkie, but his post is all about “Unambiguous date formats” in T-SQL. If you work with applications that are used by people in different countries, read this.



I’m the guy writing this blog post, and like the Midnight DBAs I apparently also prefer work to sleep—it’s currently almost 1:00 a.m. and I’m starting to wonder about just how fuzzy my brain is going to be in the morning. Luckily, I shared “Exploring ‘Fuzzy’ Interval Islands Using SQLCLR”, so it may be a non-issue.



Allen Kinsel would like to thank the PASS program committee. And what does this have to do with date and time tricks? Not much, but he sneaks through the door by providing a link to a video about time zones. Okay, Allen, that’ll do.



Jonathan Kehayias is interested in better tracking data across date and time ranges. He doesn’t ramble much (at all) in his post “Splitting Date/Time Ranges and Intersections”. Useful stuff, for sure.



Stuart Ainsworth surprised me with his post. Why? Because it’s the only one that covered data modeling, one of the more interesting aspects of the date and time question. His very interesting post is titled “Date/Time Issues and Data Modeling”.



I’m not quite certain that John Sterrett’s post is on topic for this month’s T-SQL Tuesday, but we’ll let it slide. His title is “Disk usage monitoring with Data Collector”, and he does at least mention time intervals. Good enough?



Aaron Nelson kept things quite true to theme, posting a grab-bag of “Date, Time, tricks with the DateTime Data Type”. His post includes a number of snippets—perhaps you’ll find something that will help you solve a problem you’re currently facing?


… And that’s it for this month’s T-SQL Tuesday! Next month will once again be hosted on this blog. Watch for the invitation post around January 4th. And please leave a comment here if you have any topic suggestions—I could definitely use some help coming up with a great list so that we can keep things rolling.



Update: There are a few posts by people who didn’t properly link back to the original. I’ll give them a one-time pass, because it’s the holiday season and I’m a nice guy. Click through to read posts by Alexander Kuznetsov, Peter Larsson, and Rajib Bahar.

Published Wednesday, December 9, 2009 8:00 AM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



SQLvariant said:


Huge thanks for championing this effort.  I love the idea, all these minds coming together on one topic at once from so many different angles :-)

December 9, 2009 8:58 AM

John Sterrett said:

Sorry Adam I thought it would be nice to go a little outside the box on this one. I will try to stay inside the box on the next one.

I believe estimating and/or troubleshooting disk usage is all about dates?  

December 9, 2009 9:18 AM

Alexander Kuznetsov said:


Can you add Peter Larsson, who also participated?

December 9, 2009 9:37 AM

AaronBertrand said:

Sorry I did not participate this time; I have been swamped with a major database migration.  I was going to submit a link to this earlier post but I thought that may be a little too lazy:

December 9, 2009 9:50 AM

Adam Machanic said:

Alex: Peter did not follow the rules and link back to the original post. Neither did you, for that matter--I just noticed that you posted one as well. I will update the post later today and include all of those who didn't properly notify me of their posts...

Anyone else know of a post I'm missing here?

December 9, 2009 10:01 AM

jamiet said:


I suspect Kalen will be none too pleased with the photo you posted of her :)

December 9, 2009 10:34 AM

Adam Machanic said:

LOL! Apparently Windows Live Writer messed the post up. Okay, will fix that too.

December 9, 2009 10:55 AM

daveballantyne said:

LOL , i have no idea who's photo you posted but thats not me.

December 9, 2009 11:01 AM

Adam Machanic said:


Send me a link to a photo... I found that one via Google, and since it was on a page with an image of Tony Rogerson I assumed it was the correct Dave Ballantyne.

December 9, 2009 11:09 AM

David Leibowitz (SummitCloud) said:

Adam, Great stuff. Thanks for initiating this. Frankly, this round-up highlight reads a lot better than most e-mail newsletters I recieve.

Hmm...there' a thought. End of month email syndication?

December 9, 2009 1:03 PM

AJ said:

I did not write a blog article yesterday but I did enjoy reading the posts, at least the ones I could get to before this other thing I do (work) got in the way.

Thanks for starting this off. I look forward to next month.

December 9, 2009 1:40 PM

Alejandro Mesa said:


I think that "T-SQL Tuesday" is a great idea, and I really enjoyed the posts for this round.

This is a great site for the SQL Server community, no doubt.


December 9, 2009 3:52 PM

Adam Machanic said:

Hi Alejandro,

It's really too bad that you don't have a blog so that you could join in...

December 9, 2009 4:12 PM

Kalen Delaney said:

Congratulations, Adam on a very successful event, not to mention a brilliant idea! I am honored to have been a part of it, and would be delighted to host one coming up in a future month.


December 10, 2009 2:34 AM

Aaron Bertrand said:

Sorry I have been quiet recently; been working very hard for the past two weeks on a migration of an

December 12, 2009 9:58 PM

Michael Coles: Sergeant SQL said:

Invitation for T-SQL Tuesday #006: "What About BLOB?" It's getting warm outside just in time for the

May 3, 2010 7:56 PM

Adam Machanic said:

Just over a year ago I kicked off T-SQL Tuesday , "a recurring, revolving blog party." The idea was simple:

December 7, 2010 1:43 PM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement