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:
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.
I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…
The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…
Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…
In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…
I wrote a piece of SQL that had some new T-SQL syntax in it: IS…
I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…