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

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.…

5 days ago

dataMinds Connect 2024 – Session Materials

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

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago