Convert datetime value from one time zone to UTC time zone using SQL query

Question

I have a datetime value. This datetime value can be in any time zone, for example, Eastern Standard Time or Indian Standard Time. I want to convert datetime value to UTC timezone in sql. Here, from the time zone, this parameter will be. I can achieve this using C # code. But I need it in sql query.

Can someone tell me how can I convert it?

Answer 1

The offset of the time zone and the time zone are two different things. The time zone may have different offsets if daylight saving time is used. Timezone support was added to SQL Server in the latest version of 2016.

Your question has two parts: how to convert a value datetimeto a value with an offset / time zone, and then how to convert that value to UTC.

In versions prior to SQL Server 2014, you must first determine the correct offset for your local time zone, for example, using C # code. After that, you can convert datetimeto a `datetimeoffset with a special offset with TODATETIMEOFFSET :

select TODATETIMEOFFSET(GETDATE(),'02:00')

or

select TODATETIMEOFFSET(GETDATE(),120)

This will return the value datetimeoffsetwith the original time and the specified offset.

(, UTC) SWITCHOFFSET

select SWITCHOFFSET(@someDateTimeOffset,0)

select SWITCHOFFSET(TODATETIMEOFFSET(GETDATE(),120),0)

. , SomeTime,

select SWITCHOFFSET(TODATETIMEOFFSET(SomeTime,@offsetInMinutes),0)

SQL Server 2016 . , , UTC:

SELECT (getdate() at time zone 'Central Europe Standard Time') AT TIME ZONE 'UTC'

AT TIMEZONE a datetimeoffset +2:00, UTC.

, , datetime datetimeinfo. SQL Server , , .. , - . .NET datetimeoffset, - .

Answer 2

SQL Server 2016, AT TIME ZONE:

SELECT SalesOrderID, OrderDate,   
    OrderDate AT TIME ZONE 'Eastern Standard Time' AS OrderDate_TimeZoneEST,  
    OrderDate AT TIME ZONE 'Eastern Standard Time'   
    AT TIME ZONE 'UTC' AS OrderDate_TimeZoneUTC  
FROM Sales.SalesOrderHeader;