How to assume a DATETIME is in my local time zone, then convert to UTC

Question

Using SQL Server 2016, I have a date_time column of type DATETIME that was inserted using the machine's local time zone. I need to convert this value to UTC.

I tried using

CONVERT(NVARCHAR(19), date_time AT TIME ZONE 'UTC', 127) + 'Z' AS Timestamp

but I realize now that it automatically assumes the date_time is already in UTC time so no conversion is made. I am not using DATEADD because it does not account for DST.

Is there a way I can tell SQL Server that the date time is in the machine's local time zone, then use AT TIME ZONE to convert it to UTC?

Edit: This is not a duplicate, since a comment in the approved answer ("Yea, but it assumes that the input time is UTC, which is pointless if you want to convert from local time to UTC") states the problem I have outlined in this question.

Possible duplicate of [TSQL: How to convert local time to UTC? (SQL Server 2008)](https://stackoverflow.com/questions/1205142/tsql-how-to-convert-local-time-to-utc-sql-server-2008) — David Browne - Microsoft, Apr 25 '19 at 13:26
@DavidBrowne-Microsoft - I edited the original post. This is not a duplicate of that question, since a comment in the approved answer ("Yea, but it assumes that the input time is UTC, which is pointless if you want to convert from local time to UTC") states the problem I have outlined in this question. — lzdyzydy, Apr 25 '19 at 13:34
There is no generic solution to this problem. Proof: If you are located within the EU and `date_time` contains `2019-10-27 02:30` "local time"---which occurs twice, due to clocks being reset at 03:00 to 02:00---, there are *two* possible matching UTC times. — Heinzi, Apr 25 '19 at 13:39
Do you have the ability to change the data type of the captured date from `DATETIME` to `DATETIMEOFFSET`? It's going to be challenging, if not impossible, to retrofit a solution without knowing the base time zone of the legacy dates in the system. — Eric Brandt, Apr 25 '19 at 13:41

Jeroen Mostert · Accepted Answer · 2020-01-20T17:16:33.517

AT TIME ZONE is one half of the equation. The other is getting the machine's time zone, which even 2017 has no actual function or SERVERPROPERTY for -- you need to poke around in the registry. From SQL Server 2019 onwards (and Azure SQL), there's the CURRENT_TIMEZONE() function, but (in a seemingly bizarre oversight) the return value of this cannot be used as the input to an AT TIME ZONE clause or correlated with sys.time_zone_info, so it's not clear what purpose that's supposed to serve.

DECLARE @TimeZone NVARCHAR(4000);
EXEC master.dbo.xp_regread 
    'HKEY_LOCAL_MACHINE', 
    'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 
    'TimeZoneKeyName', 
    @TimeZone OUT;

-- For me, that's 'W. Europe Standard Time'

SELECT {ts '2019-03-31 02:00:00' } AT TIME ZONE @TimeZone AT TIME ZONE 'UTC'
-- Yields '2019-03-31 01:00:00.000 +00:00', DST was not in effect

SELECT {ts '2019-03-31 03:00:00' } AT TIME ZONE @TimeZone AT TIME ZONE 'UTC'
-- *Also* yields '2019-03-31 01:00:00.000 +00:00', DST was in effect

These examples illustrate the peril of having to convert time stamps that weren't recorded with actual offsets: in the "twilight zone" of DST turnover, you get times that are inherently ambiguous. The results of such adjustments are only mostly correct, skipping and duplicating an hour twice a year. Some data sets can live with that, some can't.

score 1 · Answer 2 · answered Apr 25 '19 at 13:34

You just have to tell the query what timezone the initial date is in, with an AT TIME ZONE clause after the initial value, and then the one you want to convert it to, with a second AT TIME ZONE clause after that.

The default return value is a DATETIMEOFFSET, so you'll have to CAST/CONVERT if you want to drop the offset value. I'm in the US Central Time Zone, so I used that. You'll have to substitute the correct timezone for your server.

DECLARE @date_time DATETIME = GETDATE();

SELECT 
  @date_time AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'UTC' AS utc_time_as_datetimeoffset;

SELECT 
  CAST(@date_time AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'UTC' AS DATETIME) AS utc_time_as_datetime;

Results:

+--------------------------------+
|   utc_time_as_datetimeoffset   |
+--------------------------------+
| 2019-04-25 13:29:01.057 +00:00 |
+--------------------------------+

+-------------------------+
|  utc_time_as_datetime   |
+-------------------------+
| 2019-04-25 13:29:01.057 |
+-------------------------+

Thank you for providing an answer, but I can't assume what the initial timezone is, since the servers this will be used on can be different timezones. — lzdyzydy, Apr 25 '19 at 13:36
Continuing in comments under the question, in case I end up deleting this answer since it doesn't seem to suit your needs. — Eric Brandt, Apr 25 '19 at 13:42
This explanation combined with the above answer helped me get and understand a solution. Thank you. — lzdyzydy, Apr 25 '19 at 14:39

How to assume a DATETIME is in my local time zone, then convert to UTC

2 Answers2