Categories: TSQL

Convert Local Datetime to UTC (pre-SQL Server 2016)

Recently I was working with a client who was still on SQL Server 2012. Support issues aside, there are some very useful T-SQL functions/clauses who didn’t exist in 2012, but were introduced in a later version. One of them is AT TIME ZONE, which you can use the convert dates between time zones, as demonstrated in the blog post Converting a Datetime to UTC. But alas, no such thing in SQL Server 2012. I searched the web a bit, and most suggestions took the route of CLR.

Yeah, I favor a pure T-SQL approach instead. On a StackOverflow thread, I found the inspiration for the following script:

CREATE FUNCTION dbo.ConvertLocalDateToUTC(@localDate DATETIME)
RETURNS TABLE
AS
RETURN
WITH cte_dstperiod AS
(
SELECT
     dststart   = DATEADD(HOUR, 2, --> starts at 2 o'clock
                        DATEADD(DAY
                                ,1 - DATEPART(WEEKDAY --> assumes datefirst is set to 7 (Sunday is first day of the week)
                                             ,DATEADD(DAY, -1, DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))) --> March 31
                                ,DATEADD(DAY, -1, DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))))
    ,dstend     = DATEADD(HOUR, 2, --> starts at 2 o'clock
                        DATEADD(DAY
                                ,1 - DATEPART(WEEKDAY --> substract the number of days to go back to the last Sunday of the month
                                             ,DATEADD(DAY, -1, DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))) --> October 31
                                ,DATEADD(DAY, -1, DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))))
)
,   cte_dst AS
(
    SELECT
         dst    = IIF(@localDate >= dststart AND @localDate < dstend,1,0) --> is the supplied date in a DST period?
        ,offset = +60 --> CET = UTC + 1 (60 minutes)
    FROM cte_dstperiod
)
,   cte_offset AS
(
    SELECT
         dstFlag        = dst
        ,offset
        ,localdate      = @localDate
        ,localdateTZ    = IIF(dst = 1
                            ,TODATETIMEOFFSET(@localDate, offset + 60)
                            ,TODATETIMEOFFSET(@localDate, offset))
    FROM cte_dst
)
SELECT
     dstFlag    
    ,offset     
    ,localdate  
    ,localdateTZ
    ,UTCdate = CONVERT(DATETIME,localdateTZ,1)
FROM cte_offset;

It’s an inline table-valued function (yay performance) which takes the local datetime as an input parameter. The offset (configured in the CTE cte_dst) is set to 60 minutes, which is my time zone (CET which is GMT + 1). If you need something else, you can change it to whatever offset your time zone is in. There are two major assumptions:

  • the datefirst system setting is set to 7 (aka Sunday). This is because it is the default in most systems. If you need something else, you might want to use SET DATEFIRST before you call the function, or you adapt the logic in the first CTE.
  • daylight savings time starts on the last Sunday of March, and ends on the last Sunday of October. Again, adapt if necessary.

If those assumptions don’t work for you, you can just create a reference table with all the start and end dates of the DST for all the necessary years. I didn’t do that because I’m lazy and because I also want to leave the client with a script where they didn’t need to remember to update a table once in a while.

You can find the script on Github as well.


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Recent Posts

Book Review – Microsoft Power BI Performance Best Practices

I was asked to do a review of the book Microsoft Power BI Performance Best…

5 days ago

Create a Numbers Table in Power Query

This is a quick blog post, mainly so I have the code available if I…

1 week ago

Microsoft finally adds Tenant Switcher for Fabric / Power BI

Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…

1 week ago

Book Review – Humanizing Data Strategy by Tiankai Feng

This book was making its rounds on social media, and the concept seems interesting enough…

2 weeks ago

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

4 weeks ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

2 months ago