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.

The post Convert Local Datetime to UTC (pre-SQL Server 2016) first appeared on Under the kover of business intelligence.