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 loaded some built-in sample data from Wide World Importers into a Fabric warehouse. You…
It was great being at dataMinds Saturday 2024 this past weekend. A great crowd of…
Today I was having a nice discussion with some colleagues about Fabric and pricing/licensing came…
I recently purchased and read the book Deciphering Data Architectures - Choosing Between a Modern…
A while ago I had a little blog post series about cool stuff in Snowflake. I’m…
I have the pleasure to announce I'll be presenting at two conferences this spring. The…
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?