Convert a DATETIME value using string Timezone Info?

Question

I have a column which has date, time, and timezone info like this:

Sat, 20 Aug 2016 03:50:04 CDT
Sun, 17 Sep 2017 16:51:46 CEST
Mon, 09 Oct 2017 06:43:27 CET
Thu, 28 Sep 2017 14:05:23 IST
Thu, 05 Oct 2017 08:59:01 IDT
Wed, 28 Jun 2017 17:10:22 CEST
Mon, 18 Sep 2017 18:32:52 GMT
Sat, 27 Aug 2016 07:06:22 MEST
Sat, 06 Aug 2016 13:17:09 UTC
Sat, 16 Jul 2016 23:53:02 PDT

I want to normalize these all to UTC.

I can strip off the leading 5 chars, and trailing timezone string, and they cast to datetime just fine... but my question is:

Does SQL Server have any built in function where I can pass in the datetime, the source timezone string e.g. PDT and a target timezone string e.g. UTC and it will handle the conversion?

I am thinking of something similar to what Oracle has:

FROM_TZ(YOUR_TIMESTAMP, 'UTC') AT TIME ZONE 'America/Dawson_Creek'

I am hoping to be able to do it without having to maintain a timezone offsets table to map the string values to an offset. I also don't want to hard-code the offsets in some big case-statement.

I am on sql server 2014, so I can't use the new select * from sys.time_zone_info for timezone info that comes in 2016.

Any thoughts?

Answer 1

If your time zones are IANA time zones and not Windows time zones, then you should be able to use Matt Johnson's "SQL Server Time Zone Support" project, hosted on GitHub at:

https://github.com/mj1856/SqlServerTimeZoneSupport

It is a pure T-SQL solution.

Answer 2

This is possible with new feature in SQL Server 2016 - AT TIME ZONE.

WITH PeopleAndTZs AS
(
  SELECT * FROM (VALUES 
    ('Rob',   'Cen. Australia Standard Time'),
    ('Paul',  'New Zealand Standard Time'),
    ('Aaron', 'US Eastern Standard Time')
  ) t (person, tz)
)
SELECT tz.person, o.SalesOrderID, o.OrderDate AT TIME ZONE 'UTC' AT TIME ZONE tz.tz
FROM PeopleAndTZs tz
CROSS JOIN Sales.SalesOrderHeader o
WHERE o.SalesOrderID BETWEEN 44001 AND 44010;

A nice article https://sqlperformance.com/2016/07/sql-plan/at-time-zone.