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.

Next Exporting Environment Variables out of the SSIS Catalog »

Previous « Optimize for Unknown for Inline Table-Valued Functions

View Comments

Dave Boltman says:

September 29, 2020 at 12:12 pm

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';
- Koen Verbeeck says:
  
  September 29, 2020 at 12:18 pm
  
  You are absolutely right!
Anand nareshwar says:

January 19, 2024 at 1:53 pm

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.
- Koen Verbeeck says:
  
  January 22, 2024 at 3:31 pm
  
  I guess in that case you'll need to determine the timezone for the current user and pass it as a parameter?
Mike G says:

March 6, 2025 at 6:09 pm

Perfect for what I needed, thanks!
aitoolsonar says:

April 25, 2026 at 11:50 am

Yeah, I’ve definitely fallen for that DATEDIFF trick before—seems clean until you realize it breaks twice a year with DST. The AT TIME ZONE approach is so much cleaner, especially now that you can just specify the source timezone and let SQL handle the offset. Do you know if there’s a good way to handle older SQL Server versions for this?