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

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

5 days ago

dataMinds Connect 2024 – Session Materials

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

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago