UTC to local times in SQL Server 2016

Recently, I've needed to to do a number of conversions between UTC and local times within SQL Server. When I first looked into this I struggled to find clear explanations for going from UTC to local or from local to UTC - perhaps in part because at time zone is new in SQL Server 2016.

I got started from Thomas LaRock's article about the new 2016 functionality. The CTE at the end of that article shows how you can grab time zone information from the sys.time_zone_info DMV, and this is a great starting point.

If you've run Thomas's query you'll have noticed the use of at time zone, which returns a datetimeoffset. In case you're not familiar with this data type, let's take a moment to understand it.


Run select SysDateTimeOffset(); and you should see a date and time which mirrors your server's current time, plus a time zone offset showing its current offset from UTC; this includes any time zone offset, plus any daylight savings time offset.

If I were to run this (from the UK) on August 15th, 2017 while my clock is showing that it's noon exactly, I would get 2017-08-15 12:00:00.0000000 +01:00; the +01:00 offset is because the UK is offset by one hour from UTC during daylight savings. The datetime2 portion of a datetimeoffset is in local time, not UTC.


Getting back to at time zone, be aware that as per the MS documentation, it has two different behaviours. If you supply a datetime2 and a time zone, it will append the relevant offset from UTC for that time zone to the date and time you supplied. This behaviour assumes that you are supplying the local time, and are telling SQL Server which time zone it is from. Note that the offset appended will take any relevant daylight savings time into account:

select  
  [Jan] = Convert(datetime2(0), '2017-01-01 12:00:00')at time zone 'GMT Standard Time',
  [Aug] = Convert(datetime2(0), '2017-08-15 12:00:00')at time zone 'GMT Standard Time';
Results:  
|Jan                       |Aug                       |
|2017-01-01 12:00:00 +00:00|2017-08-15 12:00:00 +01:00|

However, if you supply a datetimeoffset and a time zone, at time zone will return a datetimeoffset of the local time in the time zone you specified, at the time you specified, and with the correct offset from UTC. In short, you can pass in a time from one time zone, and get the time at a different time zone - but you have to use datetimeoffset. Again, the offset returned will take daylight savings time into account:

select  
  [Jan] = Convert(datetimeoffset(0), '2017-01-01 12:00:00 +00:00')at time zone 'GMT Standard Time',
  [Aug] = Convert(datetimeoffset(0), '2017-08-15 12:00:00 +00:00')at time zone 'GMT Standard Time';
Results:  
|Jan                       |Aug                       |
|2017-01-01 12:00:00 +00:00|2017-08-15 13:00:00 +01:00|

With the basics explained, let's convert between UTC and local times!

  • Use at time zone to return a UTC datetimeoffset from your initial datetime2 plus the UTC time zone.
  • Use at time zone to return a local datetimeoffset from the above plus the local time zone.
  • Use Convert() with style 0 to return a local datetime2 from the above.

Here's the code - but read on if you want an explanation of that Convert() step:

with
[cte_TimeZone] as
(select
   [TimeZoneName] = [tzi].[name],
   [CurrentOffset] = [tzi].[current_utc_offset]
   from [sys].[time_zone_info] as [tzi])

select
  [TimeZoneName] = [tz].[TimeZoneName],
  [CurrentOffset] = [tz].[CurrentOffset],
  [UtcDatetime] = SysUtcDateTime(),
  [UtcDatetimeOffset] = SysUtcDateTime() at time zone 'UTC',
  [LocalDatetimeOffset] = SysUtcDateTime() at time zone 'UTC' at time zone [tz].[TimeZoneName],
  [LocalDatetime] = Convert(datetime2, SysUtcDateTime() at time zone 'UTC' at time zone [tz].[TimeZoneName], 0)
  from [cte_TimeZone] as [tz]; 

Run the above and check the results for the row with your local time zone to confirm that the column LocalDatetimeOffset is showing your correct local time and offset - it should be identical to the result you get when you run select SysDateTimeOffset();. LocalDatetime should likewise be showing your current local date and time.


So how about the reverse, local to UTC?

  • Use at time zone to return a local datetimeoffset from your initial datetime2 plus the local time zone.
  • Use Convert() with style 1 to return a UTC datetime2 from the above.

I'm going to continue using the above query to demonstrate converting from local time to UTC, but this would really be used if you had a local datetime or datetime2 stored in a table:

with
[cte_TimeZone] as
(select
   [TimeZoneName] = [tzi].[name],
   [CurrentOffset] = [tzi].[current_utc_offset]
   from [sys].[time_zone_info] as [tzi]),

[cte_LocalTime] as
(select
  [TimeZoneName] = [tz].[TimeZoneName],
  [CurrentOffset] = [tz].[CurrentOffset],
  [LocalDatetime] = Convert(datetime2, SysUtcDateTime() at time zone 'UTC' at time zone [tz].[TimeZoneName], 0)
  from [cte_TimeZone] as [tz])

select
  [TimeZoneName] = [lt].[TimeZoneName],
  [CurrentOffset] = [lt].[CurrentOffset],
  [LocalDatetime] = [lt].[LocalDatetime],
  [LocalDatetimeOffset] = [lt].[LocalDatetime] at time zone [lt].[TimeZoneName],
  [UtcDatetime] = Convert(datetime2, [lt].[LocalDatetime] at time zone [lt].[TimeZoneName], 1)
from [cte_LocalTime] as [lt];

You'll notice there was one less step this time, and that's due to how Convert() works in this situation. This is hinted at, but not fully explained, in the Microsoft documentation for CAST and CONVERT:

Beginning with SQL Server 2012, the only styles that are supported when converting from date and time types to datetimeoffset are 0 or 1. All other conversion styles return error 9809.

It seems like this is also true in reverse, when converting from datetimeoffset to date and time types. Sadly, the documentation doesn't explain what styles 0 and 1 do - but the results of the above queries show the following:

  • Style 0 truncates the offset, leaving the datetime2 part unchanged.
  • Style 1 combines the offset with the datetime2 part, resulting in a datetime2 representing the time in UTC.

You can run a very simple test of this:

select  
  [StyleZero] = Convert(datetime2(0), Convert(datetimeoffset(0), '2017-08-15 12:00:00 +01:00'), 0),
  [StyleOne] = Convert(datetime2(0), Convert(datetimeoffset(0), '2017-08-15 12:00:00 +01:00'), 1);
Results:  
|StyleZero          |StyleOne           |
|2017-08-15 12:00:00|2017-08-15 11:00:00|

I've been unable to find official documentation of this functionality; if you have done, let me know and I'll edit this article to include it.

You can instead replicate the local-to-UTC steps in reverse by using at time zone 'UTC' on your local offset to get the UTC offset, then converting with style 0 to truncate, but this is an unnecessary extra step given the Convert() functionality.

Comments