I played around with a couple ways to convert the results to the appropriate local time. The first way involved calculating the timezone offset and applying it to the datetime value.
SELECT CONVERT(DATETIME, '2020-03-10 13:30') AS UTC,
CONVERT(DATETIME, switchoffset(convert(datetimeoffset, '2020-03-10 13:30'), DATENAME(TZOffset, sysdatetimeoffset()))) as LocalDateTime;
This results are:
UTC LocalDateTime
----------------------- -----------------------
2020-03-07 13:30:00.000 2020-03-07 08:30:00.000
(1 row affected)
The LocalDateTime field calculates the timezone offset to be -5 and subtracts that from the UTC date returning the correct value. The problem is when you run into Daylight Savings Time (DST). For example, if I run the above function today (3/10/2020) which is currently in DST, I get an offset of -5. But if I apply want to convert a date prior to DST, for example last Tuesday (3/3/2020) I will still get a value of 8:30. However, the offset on 3/3/2020 should be -6.
There is new functionality available with SQL Serve 2016 plus that takes DST into consideration and is much simpler to use. To do this you use the AT TIME ZONE clause (https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15). You can take a time in UTC and specify which timezone you want it converted to. To see this in action, look at the following two queries.
SELECT CONVERT(datetime, '2020-03-07 13:30') UTC, CONVERT(DATETIMEOFFSET, '2020-03-07 13:30') AT TIME ZONE 'Central Standard Time' AS LocalDateTime;
SELECT CONVERT(DATETIME, '2020-03-10 13:30') UTC, CONVERT(DATETIMEOFFSET, '2020-03-10 13:30') AT TIME ZONE 'Central Standard Time' AS LocalDateTime;
UTC LocalDateTime
----------------------- ----------------------------------
2020-03-07 13:30:00.000 2020-03-07 07:30:00.0000000 -06:00
(1 row affected)
UTC LocalDateTime
----------------------- ----------------------------------
2020-03-10 13:30:00.000 2020-03-10 08:30:00.0000000 -05:00
(1 row affected)
The first query is not in DST and converts the value 3/7/2020 13:30 to the correct value 3/7/2020 07:30. Note the value returned is now is off the datetimeoffset datatype withe an offset of -6
The second query takes into consideration the change in DST and converts the value to 3/7/2020 8:30. This is using an offset of -5.
Similarly, you can easily convert from a local time to UTC. To do this you just apply the at time zime twice. The first converts your datetime value to a datetimeoffset datatype with the correct offset, and the second then converts that value to UTC.
SELECT(CONVERT(DATETIME,'2020-03-10 8:30') AT TIME ZONE 'Central Standard Time') AT TIME ZONE 'UTC';
----------------------------------
2020-03-10 15:49:17.393 +00:00
(1 row affected)
To find your specific time zone you can query the sys.time_zone_info view. This includes the current_utc_offset and if the timezone is in DST.
SELECT * FROM sys.time_zone_info WHERE name LIKE '%Central%'
name current_utc_offset is_currently_dst
---------------------------------- ------------------ ----------------
Central America Standard Time -06:00 0
Central Standard Time -05:00 1
Central Standard Time (Mexico) -06:00 0
Canada Central Standard Time -06:00 0
Central Brazilian Standard Time -04:00 0
Central Europe Standard Time +01:00 0
Central European Standard Time +01:00 0
W. Central Africa Standard Time +01:00 0
Central Asia Standard Time +06:00 0
N. Central Asia Standard Time +07:00 0
Aus Central W. Standard Time +08:45 0
AUS Central Standard Time +09:30 0
Central Pacific Standard Time +11:00 0
(13 rows affected)
You can also use the function CURRENT_TIMEZONE_ID() to retrieve the local servers time zone.
Hope this helps!
