T-SQL Tuesday is a fantastic series of blog posts derived from over 130 topics over the past 11 years, inviting bloggers to share their thoughts on a particular theme once a month. I’ve even participated in a couple of them myself.
Unfortunately, I keep missing the deadline, plus my blog publishes every Wednesday which is too late to take part. (The reason is boring: both my editors over the years prefer having Monday and Tuesday to edit my posts because sometimes I only have them written as late as Sunday.)
I have therefore decided to play catch-up. Over the course of the next few years I will write one post a week for each T-SQL Tuesday topic that has existed, in sequence. These may be pre-empted by other posts I feel like writing, so it should take me just over three years. By the time 2024 rolls around I should be caught up.
Why would I do this? The simple answer is that I’ve been writing a post here every week since November 2015, and I’m running out of ideas. When I was scratching my journalism itch I could write 1,000 words on any topic. This is a personal challenge to continue that tradition, and a way to keep this blog going with regular content for the foreseeable future. Who knows? By the end of the challenge I might have enough content to publish another book or two.
T-SQL Tuesday Retrospective #001: Date/Time Tricks
On 31 December 2009, Adam Machanic invited us to write about tricks involving dates and times. Back then in 2009, SQL Server 2008 was the latest release which meant that DATE, DATETIME2, and TIME data types were still new. I have written a significant amount about these data types, but not much in the way of interesting tricks.
There is one thing however that I asked Twitter earlier this year (2020 — 11 years after that first invite) that I’d like to share here involving UTC and local times. The background is that the ETL process for a customer receives data from an API in local time format (i.e. there is no offset included), but the data is assumed to be in UTC.
What I needed to do during the transformation phase of the ETL process was to convert the dates and times into a DATETIMEOFFSET data type, so that any process that uses that data would unambiguously know that it was in UTC.
Here’s the original problem statement:
#sqlhelp How do I let my T-SQL know that the datetime2 field I have actually contains a UTC value, so that I can convert it to a local time? In other words, how do I keep 2020-03-12 19:44:22.122 as the value in the column, but have SQL treat it as UTC?
There were some good suggestions on the thread, but I went with using the SWITCHOFFSET function suggested by Rob Farley (blog | Twitter), who has incidentally participated in every T-SQL Tuesday himself.
It was a simple case of using the function in a SELECT statement, like so:
SELECT SWITCHOFFSET(col1, '+00:00') as UTC
The thing I struggled with was that as UTC has no offset, I originally wrote it without the leading + sign. Once I put that in, the statement worked perfectly.
It’s important to understand that this is not by any means a fast process. The SWITCHOFFSET function has to calculate the value for every row, and running it against tens of thousands of rows can take minutes instead of seconds.
However, the amount of data coming in daily is well within the acceptable timeframe for this function to do what it needs to, and is significantly faster than doing it in a separate visual design tool.
I hope you enjoyed this brief history lesson. Stay tuned next week for the next post, #002: A Puzzling Situation.
Leave your thoughts in the comments below.