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!
I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…
The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…
Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…
In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…
I wrote a piece of SQL that had some new T-SQL syntax in it: IS…
I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…
View Comments
Thanks for this useful information. So the CONVERT is also not needed! No data types are being converted to or from DATETIME here :) This is exactly the same, and simpler:
DECLARE @dt DATETIME = '2020-02-22 22:23:13.920';
SELECT @dt AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC';
You are absolutely right!
Hello, Great post. We have a situation where we the timezone can be different for our clients. In that case how can we convert to UTC? is there a way around? Thanks in advance.
I guess in that case you'll need to determine the timezone for the current user and pass it as a parameter?