Categories: TSQL

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!


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

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';

  • 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?

Recent Posts

Book Review – Microsoft Power BI Performance Best Practices

I was asked to do a review of the book Microsoft Power BI Performance Best…

3 days ago

Create a Numbers Table in Power Query

This is a quick blog post, mainly so I have the code available if I…

1 week ago

Microsoft finally adds Tenant Switcher for Fabric / Power BI

Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…

1 week ago

Book Review – Humanizing Data Strategy by Tiankai Feng

This book was making its rounds on social media, and the concept seems interesting enough…

1 week ago

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

3 weeks ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

1 month ago