Convert Historical Local Time to UTC Time in SQL Server

Question

I am confronting an SQL Server database which contains many DateTime values stored in local time. (Yes, this is unfortunate!) We have perhaps 5-10 years of data, which means that the UTC offset for this location will change depending on the time of year if the region in question observes Daylight Savings Time, and of course the schedule on which that change occurred may also change, as for example it did in the United States (where most of these data originate) back in 2007.

My objective is to convert these DateTimes to UTC time at the SQL level. Short of loading the entire Olson or TZ Database and querying it, does anyone have a technique for converting an historical local timestamp to a UTC time? [If it helps, conveniently, we happen to have the latitude and longitude for each row as well (could be used to identify timezone.]

Note: for a row written in real time, the trick of DATEDIFF(Hour, Getutcdate(), GETDATE()) AS UtcOffset works fine, of course. The problem is applying this retroactively to dates that occurred on either side of the Daylight Savings Time "barrier".

Answer 1

You can use AT TIME ZONE to convert to UTC. SQL knows about the switches to daylight savings so it will account for it. You just have to figure out the timezone (using the latitude and longitude, as you said).

You can get all timezones from here: SELECT * FROM sys.time_zone_info

So the solution will be something like this: First, add a column to your table with timezone (which you find out using the latitude and longitude). Then update your (newly added) UTC date column with AT TIME ZONE, for example:

-- some sample data to play with
CREATE TABLE #YourTable
(
    LocalDateTime DATETIME,
    [UtcDateTime] DATETIMEOFFSET,
    TimeZoneName VARCHAR(100)
);

INSERT INTO #YourTable
(
    LocalDateTime,
    TimeZoneName
)
VALUES
('20150101', 'Alaskan Standard Time'),
('20150101', 'US Mountain Standard Time'),
('20190701', 'Alaskan Standard Time'),
('20190701', 'US Mountain Standard Time');


-- convert to UTC
UPDATE #YourTable
SET [UtcDateTime] = LocalDateTime AT TIME ZONE TimeZoneName AT TIME ZONE 'UTC';

-- check results
SELECT * FROM #YourTable;