Converting a Datetime to UTC

I was in a need of converting some datetime values (of which I know the actual timezone) to UTC dates. A quick Google search showed me that most results on the first page were simply wrong. Most of them used this trick:

DECLARE @dt DATETIME = '2020-09-22 22:23:13.920';
SELECT  DATEADD(MI, (DATEDIFF(MI, SYSDATETIME(), SYSUTCDATETIME())), @dt);

Seems nifty, but they forgot about daylight savings time. If I run the same query somewhere in December, I get a different result. I like my functions deterministic please.

Luckily, SQL Server 2016 introduced us to the DATETIMEOFFSET data type and the AT TIME ZONE clause. This means you can convert the input datetime to your local timezone, convert this to UTC and finally convert that result to datetime again. In code:

DECLARE @dt DATETIME = '2020-09-22 22:23:13.920';
SELECT CONVERT(DATETIME,
CONVERT(
DATETIMEOFFSET,
CONVERT(DATETIMEOFFSET
-- assuming all servers are on CEST time
,@dt AT TIME ZONE 'Central European Standard Time'
)
AT TIME ZONE 'UTC')
  );

If you’re stuck on a lower version of SQL Server, I’d suggest you upgrade Or maybe create a table with all the start and end dates of daylight savings time for each year, so you can easily look up the offset.

UPDATE:

Turns out the conversion to DATETIMEOFFSET isn’t even necessary, which makes the code even shorter:

DECLARE @dt DATETIME = '2020-02-22 22:23:13.920';
SELECT CONVERT(DATETIME,
    @dt AT TIME ZONE 'Central European Standard Time'
    AT TIME ZONE 'UTC');

Thanks to Adam for pointing it out!

The post Converting a Datetime to UTC first appeared on Under the kover of business intelligence.

Rate

Share

Share

Rate