The DATETIMEOFFSET data type allows you to store a local date and time value, including its time zone offset from UTC. T-SQL provides supporting functions called TODATETIMEOFFSET and SWITCHOFFSET to enable you to convert a nonoffset aware value plus an offset to this type and to switch the offset of an offset aware value to a target offset, respectively. However, both functions require you to specify a static target time zone offset, which depends on whether the target time is during standard time or daylight saving time, where relevant. SQL Server 2016 introduces a more flexible alternative to both functions called AT TIME ZONE, with which you specify a target time zone name, letting SQL Server figure out the applicable time zone offset dynamically based on Windows time zone conversion rules. This article covers these three functions in detail.
TODATETIMEOFFSET
The TODATETIMEOFFSET function operates on two inputs: a nonoffset date and time value and a target time zone offset, and simply merges the two to a DATETIMEOFFSET value. The input date and time value is an expression that resolves to a DATETIME2 value. The time zone offset is either a string holding the hours and minutes offset, such as '-08:00', or an integer with the minutes offset, such as -480. You could think of this function as a helper function that prevents you from needing to mess with conversion of the inputs to character strings with the right style, concatenating them, and then converting them to DATETIMEOFFSET. SQL Server does the work for you. Consider the following example:
DECLARE
@dt AS DATETIME2 = '20160812 12:00:00.0000000',
@tzoffset AS CHAR(6) = '-07:00'; -- or use the integer -420
SELECT TODATETIMEOFFSET(@dt, @tzoffset);
This code generates the following output:
2016-08-12 12:00:00.0000000 -07:00
One of the use cases of this function is when storing in a table the local date and time value and the time zone offset in two separate columns, and later needing to convert the two columns to a single DATETIMEOFFSET value. To generate the current local nonoffset date and time value in the target SQL Server instance, you use functions such as SYSDATETIME (returns DATETIME2) or GETDATE (returns DATETIME). To generate the current time zone offset, you use functions like DATENAME (returns a string with hours and minutes offset) or DATEPART (returns an integer with minutes offset) with the TZoffset part (tz in short).
Here’s an example:
SELECT
SYSDATETIME() AS curdatetime,
DATENAME(TZoffset, SYSDATETIMEOFFSET()) AS curtzoffsetstr,
DATEPART(TZoffset, SYSDATETIMEOFFSET()) AS curtzoffsetint;
I ran this code on my system, which is defined with the time zone Pacific Time, when standard time was observed, and I got the following output:
curdatetime curtzoffsetstr curtzoffsetint
--------------------------- ------------------------------ ---------------
2016-11-13 23:36:20.8893869 -08:00 -480
During standard time, the time zone offset on my system is -08:00 (-480 minutes); during daylight saving time it’s -07:00 (-420 minutes).
SWITCHOFFSET
The SWITCHOFFSET function has a different purpose than the DATETIMEOFFSET function. It operates on two inputs: a DATETIMEOFFSET value and a target time zone offset, and switches the source value’s offset to the target one. The UTC value is the same for the input and the output, it’s just that you want to switch the offset of the input values from whatever source offset they were recorded with to a desired target offset.
Here’s an example for using this function:
DECLARE
@dt AS DATETIMEOFFSET = '20160812 15:00:00.0000000 -04:00',
@tzoffset AS CHAR(6) = '-07:00'; -- or use the integer -420
SELECT SWITCHOFFSET(@dt, @tzoffset);
This code generates the following output:
2016-08-12 12:00:00.0000000 -07:00
The tricky thing about this function is that you need to know which target time zone offset to specify, and the offset is different in places that change their clocks when standard time and when daylight saving time are observed. In the above example, I wanted to switch the input value to the time zone Pacific Time. I needed to know that the switched value falls in a period when daylight saving is observed, and hence use -07:00 as the target time zone offset. But, as you can realize, if you’re querying a table that holds input DATETIMEOFFSET values in a column, some values will need to be switched to -07:00 (when target is during daylight saving time) and some to -08:00 (when target is during standard time).
AT TIME ZONE
The AT TIME ZONE function was introduced in SQL Server 2016. It replaces both TODATETIMEOFFSET and SWITCHOFFSET. It has the following syntax:
<value> AT TIME ZONE '<standard time zone name>'
When the input value is a nonoffset date and time value, the function behaves similar to TODATETIMEOFFSET; when the input value is a date and time value with an offset, the function behaves similar to SWITCHOFFSET. Moreover, you don’t need to worry about clock switching; rather, just specify the target standard time zone name (for instance, for Pacific Time always specify 'Pacific Standard Time'), and SQL Server will figure out dynamically the target time zone offset based on the Windows time zone conversion rules. To get the full list of supported standard time zone names, plus their current offset from UTC and whether it’s currently daylight saving time, query the sys.time_zone_info function, like so:
SELECT *
FROM sys.time_zone_info;
Earlier I showed how you can compute your system’s current time zone offset from UTC by applying the DATENAME or DATEPART functions to SYSDATETIMEOFFSET with the TZoffset part. Unfortunately, there’s no similar simple way to compute your system’s current standard time zone name, in case you need to record it for later use with the AT TIME ZONE function.
One way to achieve this is to read the time zone name directly from the registry using the xp_regread extended stored procedure, like so:
DECLARE @tzname AS sysname;
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',
@tzname OUTPUT;
SELECT @tzname;
However, this stored procedure is not supported, and therefore this method is not recommended.
A cleaner, supported solution is to create a CLR user defined function that invokes the method System.TimeZone.CurrentTimeZone.StandardName. Here’s the C# code to define such a function called CurrentTimeZoneStandardName:
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class TimeZoneFunctions
{
[SqlFunction(IsDeterministic = false, DataAccess = DataAccessKind.None)]
public static SqlString CurrentTimeZoneStandardName()
{
return TimeZone.CurrentTimeZone.StandardName;
}
}
Assuming you created a .dll file called C:\Temp\TimeZoneFunctions\TimeZoneFunctions\bin\Debug\TimeZoneFunctions.dll with the assembly, use the following code to deploy it in SQL Server:
-- Make sure CLR is enabled
EXEC sys.sp_configure 'CLR Enabled', 1;
RECONFIGURE WITH OVERRIDE;
IF DB_ID('testtimezone') IS NULL CREATE DATABASE testtimezone;
GO
USE testtimezone; -- replace with your preferred database
DROP FUNCTION IF EXISTS dbo.CurrentTimeZoneStandardName;
DROP ASSEMBLY IF EXISTS TimeZoneFunctions;
CREATE ASSEMBLY TimeZoneFunctions
FROM 'C:\Temp\TimeZoneFunctions\TimeZoneFunctions\bin\Debug\TimeZoneFunctions.dll';
-- or instead of .dll file, use:
/*
CREATE ASSEMBLY TimeZoneFunctions
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300CC192A580000000000000000E00002210B010B000008000000060000000000005E2600000020000000400000000000100020000000020000040000000000000006000000000000000080000000020000000000000300608500001000001000000000100000100000000000001000000000000000000000000C2600004F00000000400000C802000000000000000000000000000000000000006000000C000000D42400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000064060000002000000008000000020000000000000000000000000000200000602E72737263000000C80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000402600000000000048000000020005007C20000058040000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300100150000000100001100280500000A6F0600000A280700000A0A2B00062A1E02280800000A2A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000038010000237E0000A40100009401000023537472696E67730000000038030000080000002355530040030000100000002347554944000000500300000801000023426C6F620000000000000002000001471402000900000000FA25330016000001000000080000000200000002000000080000000400000001000000010000000200000000000A00010000000000060042003B000A006A0055000600A90096000F00BD0000000600EC00CC0006000C01CC000A0045012A0106005A013B0000000000010000000000010001000100100020000000050001000100502000000000960074000A000100712000000000861890000F000100190090001300290090001900310090000F00390090000F0041006301C20041007701C70011008801CB00090090000F00200023001E002E000B00D6002E001300DF002E001B00E800D10004800000000000000000000000000000000020000000040000000000000000000000010032000000000004000000000000000000000001004900000000000000003C4D6F64756C653E0054696D655A6F6E6546756E6374696F6E732E646C6C0054696D655A6F6E6546756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670043757272656E7454696D655A6F6E655374616E646172644E616D65002E63746F720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650054696D655A6F6E65006765745F43757272656E7454696D655A6F6E65006765745F5374616E646172644E616D65006F705F496D706C6963697400000320000000000019B6CD3E12695E41B750764D1CB79E930008B77A5C561934E089040000110903200001052001011111042001010880A20100020054020F497344657465726D696E6973746963005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000004000012210320000E05000111090E04070111090801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000000CC192A5800000000020000001C010000F0240000F0060000525344531AA004ED8F58E3438101DE590490937C01000000633A5C54656D705C54696D655A6F6E6546756E6374696F6E735C54696D655A6F6E6546756E6374696F6E735C6F626A5C44656275675C54696D655A6F6E6546756E6374696F6E732E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003426000000000000000000004E26000000200000000000000000000000000000000000000000000040260000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000006C02000000000000000000006C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004CC010000010053007400720069006E006700460069006C00650049006E0066006F000000A801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000004C001600010049006E007400650072006E0061006C004E0061006D0065000000540069006D0065005A006F006E006500460075006E006300740069006F006E0073002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005400160001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000540069006D0065005A006F006E006500460075006E006300740069006F006E0073002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000603600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
*/
GO
CREATE FUNCTION dbo.CurrentTimeZoneStandardName() RETURNS sysname EXTERNAL NAME TimeZoneFunctions.TimeZoneFunctions.CurrentTimeZoneStandardName;
GO
Once deployed, use the following code to test the function
SELECT dbo.CurrentTimeZoneStandardName() AS currenttimezonestandardname;
I get the following output on my system:
Pacific Standard Time
Use the following code to get more details about your system’s current time zone:
SELECT *
FROM sys.time_zone_info
WHERE name = dbo.CurrentTimeZoneStandardName();
I got the following output:
name current_utc_offset is_currently_dst
---------------------- ------------------ ----------------
Pacific Standard Time -08:00 0
AT TIME ZONE as Dynamic TODATETIMEOFFSET
As mentioned, when applying the AT TIME ZONE function to a nonoffset date and time value, it behaves like a dynamic TODATETIMEOFFSET. In dynamic, I mean that you specify the standard time zone name, and SQL Server worries about which target time zone offset to use. When there’s no ambiguity, the output’s local time is set to the input’s local time, and the output’s offset is determined based on whether standard time or daylight saving time is observed for the specified time zone name. However, there are two periods during which there’s ambiguity concerning the local value and the time zone offset that should be set. The AT TIME ZONE function applies specific rules for values that fall in those periods with ambiguity.
One such period is when switching from standard time to daylight saving time (clock is usually set forward one hour); you have a gap of usually an hour in the local time. For example, in 2016, Pacific Time experienced a change of +1 hour from PST to PDT at '20160313 02:00:00.0000000' (clock changed from 02:00 to 03:00). If the input value falls within the skipped hour, e.g., 02:30, the AT TIME ZONE function sets the local time of the output to one hour ahead of the input (03:30 in our example), and uses the offset after the change (PDT: -07:00 in our example).
Another such period is when switching from daylight saving time to standard time (clock is usually set back one hour); you usually have an hour in the local time that repeats itself. For example, in 2016, Pacific Time experienced a change of -1 hour from PDT to PST at '20161106 02:00:00.0000000' (clock changed from 02:00 to 01:00). If the input value falls during the repeating hour, e.g., 01:30, the AT TIME ZONE FUNCTION sets the local time of the output to that of the input (01:30 in our example) and uses offset before change (PDT: -07:00 in our example).
The following code demonstrates the conversion of both unambiguous and ambiguous values:
DECLARE
@dt1 AS DATETIME2 = '20160212 12:00:00.0000000',
@dt2 AS DATETIME2 = '20160313 02:30:00.0000000',
@dt3 AS DATETIME2 = '20160812 12:00:00.0000000',
@dt4 AS DATETIME2 = '20161106 01:30:00.0000000',
@timezone AS sysname = 'Pacific Standard Time';
SELECT *
FROM ( VALUES
(@dt1, @dt1 AT TIME ZONE @timezone),
(@dt2, @dt2 AT TIME ZONE @timezone),
(@dt3, @dt3 AT TIME ZONE @timezone),
(@dt4, @dt4 AT TIME ZONE @timezone) ) AS D(dt, dto);
This code generates the following output:
dt dto
--------------------------- ----------------------------------
2016-02-12 12:00:00.0000000 2016-02-12 12:00:00.0000000 -08:00 -- no ambiguity
2016-03-13 02:30:00.0000000 2016-03-13 03:30:00.0000000 -07:00
2016-08-12 12:00:00.0000000 2016-08-12 12:00:00.0000000 -07:00 -- no ambiguity
2016-11-06 01:30:00.0000000 2016-11-06 01:30:00.0000000 -07:00
With time zones that don't change clocks like 'US Mountain Standard Time' (used in Arizona) there's no ambiguity. The local time of the output is set to that of the input, and the offset of the specified time zone is used (-07:00 in this example).
AT TIME ZONE as Dynamic SWITCHOFFSET
When the input value is a DATETIMEOFFSET typed value, the AT TIME ZONE function behaves like a dynamic SWITCHOFFSET function. It switches the time zone offset of the input value to the target named time zone’s offset based on whether the target value falls during daylight saving time or standard time.
Consider the following example:
DECLARE
@dt1 AS DATETIMEOFFSET = '20160212 12:00 -08:00', -- before PST->PDT
@dt2 AS DATETIMEOFFSET = '20160313 01:30 -08:00', -- before PST->PDT, after EST->EDT
@dt3 AS DATETIMEOFFSET = '20160313 03:30 -07:00', -- after PST->PDT
@dt4 AS DATETIMEOFFSET = '20160812 12:00 -07:00', -- before PDT->PST
@dt5 AS DATETIMEOFFSET = '20161106 01:30 -07:00', -- before PDT->PST, after EDT->EST
@dt6 AS DATETIMEOFFSET = '20161106 01:30 -08:00', -- after PDT->PST
@timezone AS sysname = 'Eastern Standard Time';
SELECT *
FROM ( VALUES
(@dt1, @dt1 AT TIME ZONE @timezone),
(@dt2, @dt2 AT TIME ZONE @timezone),
(@dt3, @dt3 AT TIME ZONE @timezone),
(@dt4, @dt4 AT TIME ZONE @timezone),
(@dt5, @dt5 AT TIME ZONE @timezone),
(@dt6, @dt6 AT TIME ZONE @timezone) ) AS D(dtosrc, dtotgt);
This code generates the following output:
dtosrc dtotgt
----------------------- -----------------------
2016-02-12 12:00 -08:00 2016-02-12 15:00 -05:00 -- before PST->PDT, +03:00
2016-03-13 01:30 -08:00 2016-03-13 05:30 -04:00 -- before PST->PDT, after EST->EDT, +04:00
2016-03-13 03:30 -07:00 2016-03-13 06:30 -04:00 -- after PST->PDT, +03:00
2016-08-12 12:00 -07:00 2016-08-12 15:00 -04:00 -- before PDT->PST, +03:00
2016-11-06 01:30 -07:00 2016-11-06 03:30 -05:00 -- before PDT->PST, after EDT->EST, +02:00
2016-11-06 01:30 -08:00 2016-11-06 04:30 -05:00 -- after PDT->PST, +03:00
The source values were captured in a system set to the time zone Pacific Time, with offset -08:00 during Pacific Standard Time (PST) for values @dt1, @dt2 and @dt6, and with offset -07:00 during Pacific Daylight Time (PDT) for values @dt3, @dt4 and @dt5. This example converts the input values to the target time zone Easter Time (standard name is Easter Standard Time). During Eastern Standard Time (EST) the offset should be -05:00, and during Easter Daylight Time (EDT) it should be -04:00. The following adjustments are applied to the different input values to switch them to the target time zone:
- @dt1: Both input and output values fall during standard time. The local value is adjusted 3 hours forward.
- @dt2: The input value falls during standard time and the output value already falls during daylight saving time. The local value is adjusted 4 hours forward.
- @dt3: Both input and output values fall during daylight saving time. The local value is adjusted 3 hours forward.
- @dt4: Both input and output values fall during daylight saving time. The local value is adjusted 3 hours forward.
- @dt5: The input value falls during daylight saving time and the output value already falls during standard time. The local value is adjusted 2 hours forward.
- @dt6: Both input and output values fall during standard time. The local value is adjusted 3 hours forward.
With time zones that don't change offset, like 'US Mountain Standard Time', the local time is simply adjusted by the offset difference between the source and the target offsets. If you change the target time zone name in the above query to 'US Mountain Standard Time', you get the following:
- Source values with offset -08:00 are adjusted one hour forward and returned with offset -07:00.
- Source values with offset -07:00 remain unchanged and returned with offset -07:00.
Using the AT TIME ZONE function you can handle a common request to return the current DATETIMEOFFSET value in a specified target time zone. You apply the AT TIME ZONE function to the value returned by the SYSDATETIMEOFFSET function, with the target standard time zone name. For instance, to present the current DATETIMEOFFSET value with an offset that reflects Pacific Time, use the following code:
DECLARE @targettimezone AS sysname = 'Pacific Standard Time';
SELECT SYSDATETIMEOFFSET() AT TIME ZONE @targettimezone;
When I ran this code on my system I got the following output:
2016-11-13 23:30:26.3248001 -08:00
Multiple AT TIME ZONE Conversions
There are cases where you need to apply two conversions with the AT TIME ZONE function. That’s when you store a nonoffset date time value in a particular time zone, and you want to return it as an offset date and time value in a target time zone. For instance, suppose that you capture a nonoffset date and time value in the UTC time zone, and you want to return it as an offset value in Pacific Time. You apply the first conversion from a nonoffset value to an offset one specifying that it uses the UTC time zone, and then a second conversion to switch it to Pacific Time. Here’s an example demonstrating this:
DECLARE
@dt AS DATETIME2 = '20160812 12:00:00.0000000',
@sourcetimezone AS sysname = 'UTC',
@targettimezone AS sysname = 'Pacific Standard Time';
SELECT @dt AT TIME ZONE @sourcetimezone AT TIME ZONE @targettimezone;
This code generates the following output:
2016-08-12 05:00:00.0000000 -07:00
Conclusion
Time zone related calculations with DATETIMEOFFSET values can get quite tricky. Prior to the introduction of the AT TIME ZONE function the two main tools that we had for time zone conversions were the TODATETIMEOFFSET and SWITCHOFFSET functions. Both require you to work with a static target offset, and you need to know whether the target falls during standard or daylight saving time to know which offset to use. The AT TIME ZONE function, which was introduced in SQL Server 2016, replaces both older functions, allowing you to work with a target standard time zone name and dynamically figuring out the target offset based on the Windows time zone conversion rules. What we’re currently missing is the ability to compute the system’s current time zone name. It would be nice to see such an option added to T-SQL in the future--for instance, as a new property in the SERVERPROPERTY function.
