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

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

    539
    14

    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.

     

    m2_normal

    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!

     

    DPP_0043

    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.

     

    biggerpicture

    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?

     

    IMG_4848

    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.

     

    meSmall_normal

    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.

     

    SeanJenSm_bigger

    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_dancing_bigger

    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.

     

    adam_machanic

    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.

     

    deleteme_bigger

    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.

     

    ArmyPicture_bigger

    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.

     

    d832c4d8fb6071a0d4f16f005f3d2810

    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”.

     

    johnsterrett_bigger

    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?

     

    Dad_and_D_bigger

    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.

    Enjoy!

     

    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.

    14 COMMENTS

    1. Adam,
      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 🙂

    2. 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?  

    3. 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?

    4. Dave,
      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.

    5. 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?

    6. 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.

    7. Adam,
      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.
      AMB

    8. 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.
      ~Kalen

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here