Convert datetime value from one timezone to UTC timezone using sql query

Question

I have a datetime value. That datetime value may be in any timezone like 'Eastern Standard Time' or 'India Standard Time'. I want to convert that datetime value to UTC timezone in SQL. Here from timezone value will be the given parameter. I can achieve this using C# code also. But I need this in SQL query.

Can anyone tell me how can I convert that?

Answer 1

Timezone and timezone offset are two different things. A timezone can have different offsets if daylight savings time is used. Timezone support was added to SQL Server in the latest version, 2016.

Your question has two parts - how to convert a datetime value to a value with offset/timezone and then how to convert that value to a UTC.

In versions up to SQL Server 2014, you have to determine the correct offset for your local timezone in advance, eg using C# code. Once you have it you can convert a datetime to a `datetimeoffset with a specific offset with TODATETIMEOFFSET:

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

or

select TODATETIMEOFFSET(GETDATE(),120)

This will return a datetimeoffset value with the original time and the specified offset.

Switch to another offset (eg UTC) is performed by the SWITCHOFFSET function

select SWITCHOFFSET(@someDateTimeOffset,0)

You can combine both with

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

The offset can be passed as a parameter. Assuming your field is called SomeTime, you could write

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

In SQL Server 2016 you can use the timezone names. You still need a double conversion though, first to the local timezone then to UTC:

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

The first AT TIMEZONE returns a datetimeoffset with a +2:00 offset and the second converts it to UTC.

NOTE

You could probably avoid all conversions if you used the datetimeinfo type instead of datetime. SQL Server allows comparisons, filtering, calculations etc on values of different offsets, so you wouldn't need to make any conversions for querying. On the client side, .NET has the equivalent DateTimeOffset type so you wouldn't need to make any conversion in client code.

Answer 2

If you are using SQL Server 2016 you can use the new AT TIME ZONE clause:

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;  

Answer 3

To convert from one zone to another use AT TIME ZONE ,

Say if you have a table Book with purchasedTime stored in database as UTC , and you want to know time at EST Zone .

Select bookName , CONVERT(datetime,purchasedTime) AT TIME ZONE 'UTC' AT TIME ZONE 'US Eastern Standard Time' as purchaseTimeInEST from Book;

This results in all the books purchased in EST time zone.

Answer 4

for older then sql server 2012 use below query, it worked for me.

DECLARE @localdate DATETIME = '2020-01-10 05:30';
Declare @DateDiff bigint =Datediff(minute, Getdate(),Getutcdate() ) --GetDate -current datetime of local
--Declare @DateDiff bigint =-330  --you ca declare timezone different in minutes also
SELECT @localdate AS LocalDate, Dateadd(minute, @DateDiff, @localdate) AS UTCConvertedDate;