25

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
user1934329
  • 529
  • 1
  • 6
  • 18

5 Answers5

37

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

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • 3
    What happens if you don't know the exact offset used for that date? For example `TODATETIMEOFFSET(GETDATE(),120)` returns 60 minutes today but would have returned 120 minutes in the past (because of DST). – Salman A Dec 07 '17 at 11:54
  • 1
    No idea why it's so difficult to find references to SWITCHOFFSET. I bet it's exactly what most people are looking for. – Paschover Aug 15 '18 at 14:02
  • 2
    @Panagiotis Kanavos: This doesn't work because of daylight saving time... Because you don't know the offset at a specific date for any possible time zone. – Stefan Steiger Mar 01 '19 at 09:49
  • 1
    @StefanSteiger which one and why? DST matters only when you want to transform local time to a `datetimeoffset`, because you need to know the actual offset value to use. Once you have the offset, you're switching offsets, not timezones. As for `AT TIME ZONE`, it *does* takes DST into account. If you try the same time in winter and summer you'll get a different offset. – Panagiotis Kanavos Mar 01 '19 at 09:57
  • 1
    @StefanSteiger for example `SELECT (cast('2019-03-11 11:13' as datetime) at time zone 'Central Europe Standard Time') ` returns `2019-03-11 11:13:00.000 +01:00` but `2019-06-11` returns `2019-06-11 11:13:00.000 +02:00`. – Panagiotis Kanavos Mar 01 '19 at 09:59
  • 1
    @StefanSteiger if you mean that the *timezone names* are useless, non-standard, not used by data sources and too few, and that the IANA tz names should be used instead, I'd agree wholeheartedly. I work for an online travel agency and airlines don't post departures using *Windows* timezone names, they use city names, offsets and often, IANA timezones. So do all sources for that matter – Panagiotis Kanavos Mar 01 '19 at 10:02
  • 1
    @PanagiotisKanavos your double conversion doesnt work when the time zone is 'New Zealand Standard Time' and the UTC time is in a different day than NZT. For example right now its 2020-04-16 16:58:12 in NZT, but your method returns 2020-04-15 16:58:12 (note the incorrect day) I've added an answer below with a method that does work. – Jono Apr 16 '20 at 05:00
  • @Jono but that's what you're also doing - converting to one timezone, only to get the offset and then convert to another, using the offset instead of the name. The problem is that' `New Zealand Standard Time` is anything but - only the IANA timezone names are standardized. SQL Server stores offsets, not timezone names (standard or not), so you always have to use double conversions – Panagiotis Kanavos Apr 21 '20 at 08:27
  • how to convert all dateTime in a query no matter its name to a specific time zone ? – Bill Somen Feb 01 '21 at 22:39
  • @PanagiotisKanavos why there is no need to convert time zone whe querying date? note that `datepart` function for `datetimeoffset` returns month/day/time in the field time zone. but when comparing two `datetimeoffset` (with >=< operators), time zone is not important and you are correct. – afruzan Mar 18 '21 at 06:04
  • Very clear explanation!! – Saranya Jul 07 '22 at 02:57
8

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;
Steve Ford
  • 7,433
  • 19
  • 40
4

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.

Mithun abisheg
  • 55
  • 7
1

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;
Pandi_Snkl
  • 476
  • 5
  • 16
0

To convert to a time zone using the time zone name, you can do the following:

Find the number of minutes between the server timezone and your time zone 
-- Change to your time zone name
declare @TimezoneName varchar(max) = 'New Zealand Standard Time'
declare @timezoneOffset bigint = Datediff( MINUTE, getdate() at time zone @TimezoneName, getdate() )

Use SWITCHOFFSET to give you the datetime in your timezone.

eg when getdate() is '2020-04-16 04:47:25.640' you get '2020-04-16 16:47:25.640 +12:00' (+12 for NZT)

select SWITCHOFFSET(getdate(), @timezoneOffset)

This should work in SQL Server 2016 and above.

Jono
  • 3,949
  • 4
  • 28
  • 48
  • 1
    You're also performing a double **offset** conversion, only this time you go through a **non**-standard timezone name to get the offset, then you use an offset conversion. There's no way to avoid this, since SQL Server stores offsets only, not timezone names (standard or not) – Panagiotis Kanavos Apr 21 '20 at 08:30
  • 1
    BTW this query won't work as `GETDATE()` will return a local time. This will return different results for servers with different settings. It will definitely fail on any cloud server where the default timezone is UTC. All the more reason to *avoid* conversions by using `DateTimeOffset` – Panagiotis Kanavos Apr 21 '20 at 08:31