Bewerken

Delen via

AT TIME ZONE (Transact-SQL)

  • Artikel

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone. If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules.

AT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones.

Transact-SQL syntax conventions

Syntax

inputdate AT TIME ZONE timezone

Arguments

inputdate

An expression that can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.

timezone

Name of the destination time zone. SQL Server relies on time zones that are stored in the Windows Registry. Time zones installed on the computer are stored in the following registry hive: KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. A list of installed time zones is also exposed through the sys.time_zone_info view.

For more information about time zones for SQL Server on Linux, see Configure the time zone for SQL Server 2022 on Linux.

Return types

Returns the data type of datetimeoffset.

Return value

The datetimeoffset value in the target time zone.

Remarks

AT TIME ZONE applies specific rules for converting input values in smalldatetime, datetime, and datetime2 data types that fall into an interval affected by a DST change:

  • When the clock's set ahead, there's a gap in local time equal to the duration of the clock adjustment. This duration is usually 1 hour, but it can be 30 or 45 minutes, depending on time zone. Points in time that are in this gap are converted with the offset after DST change.

    /*
  Moving to DST in "Central European Standard Time" zone:
  offset changes from +01:00 -> +02:00
  Change occurred on March 27th, 2022 at 02:00:00.
  Adjusted local time became 2022-03-27 03:00:00.
*/

--Time before DST change has standard time offset (+01:00)
SELECT CONVERT(DATETIME2(0), '2022-03-27T01:01:00', 126)
AT TIME ZONE 'Central European Standard Time';
--Result: 2022-03-27 01:01:00 +01:00

/*
  Adjusted time from the "gap interval" (between 02:00 and 03:00)
  is moved 1 hour ahead and presented with the summer time offset
  (after the DST change)
*/
SELECT CONVERT(DATETIME2(0), '2022-03-27T02:01:00', 126)
AT TIME ZONE 'Central European Standard Time';
--Result: 2022-03-27 03:01:00 +02:00
--Time after 03:00 is presented with the summer time offset (+02:00)
SELECT CONVERT(DATETIME2(0), '2022-03-27T03:01:00', 126)
AT TIME ZONE 'Central European Standard Time';
--Result: 2022-03-27 03:01:00 +02:00

  • When the clock is set back, then 2 hours of local time are overlapped onto one hour. In that case, points in time that belong to the overlapped interval are presented with the offset before the clock change:

    /*
    Moving back from DST to standard time in
    "Central European Standard Time" zone:
    offset changes from +02:00 -> +01:00.
    Change occurred on October 30th, 2022 at 03:00:00.
    Adjusted local time became 2022-10-30 02:00:00
*/

--Time before the change has DST offset (+02:00)
SELECT CONVERT(DATETIME2(0), '2022-10-30T01:01:00', 126)
AT TIME ZONE 'Central European Standard Time';
--Result: 2022-10-30 01:01:00 +02:00

/*
  Time from the "overlapped interval" is presented with DST offset (before the change)
*/
SELECT CONVERT(DATETIME2(0), '2022-10-30T02:00:00', 126)
AT TIME ZONE 'Central European Standard Time';
--Result: 2022-10-30 02:00:00 +02:00

--Time after 03:00 is regularly presented with the standard time offset (+01:00)
SELECT CONVERT(DATETIME2(0), '2022-10-30T03:01:00', 126)
AT TIME ZONE 'Central European Standard Time';
--Result: 2022-10-30 03:01:00 +01:00

Since some information (such as timezone rules) is maintained outside of SQL Server and are subject to occasional change, the AT TIME ZONE function is classed as nondeterministic.

While datetimeoffset isn't supported in data warehousing in Microsoft Fabric, AT TIME ZONE can still be used with datetime2, as in the following example.

Examples

A. Add target time zone offset to datetime without offset information

Use AT TIME ZONE to add offset based on time zone rules when you know that the original datetime values are provided in the same time zone:

USE AdventureWorks2022;
GO

SELECT SalesOrderID, OrderDate,
    OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST
FROM Sales.SalesOrderHeader;

B. Convert values between different time zones

The following example converts values between different time zones. The OrderDate values are datetime and aren't stored with an offset, but are known to be Pacific Standard Time. The first step is to assign the known offset and then convert to the new time zone:

USE AdventureWorks2022;
GO

SELECT SalesOrderID, OrderDate,
    --Assign the known offset only
    OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST,
    --Assign the known offset, then convert to another time zone
    OrderDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'Central European Standard Time' AS OrderDate_TimeZoneCET
FROM Sales.SalesOrderHeader;

You can also substitute in a local variable containing the time zone:

USE AdventureWorks2022;
GO

DECLARE @CustomerTimeZone nvarchar(128) = 'Central European Standard Time';

SELECT SalesOrderID, OrderDate,
    --Assign the known offset only
    OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST,
    --Assign the known offset, then convert to another time zone
    OrderDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE @CustomerTimeZone AS OrderDate_TimeZoneCustomer
FROM Sales.SalesOrderHeader;

C. Query temporal tables using a specific time zone

The following example selects data from a temporal table using Pacific Standard Time.

USE AdventureWorks2022;
GO

DECLARE @ASOF DATETIMEOFFSET;

SET @ASOF = DATEADD(MONTH, -1, GETDATE()) AT TIME ZONE 'UTC';

-- Query state of the table a month ago projecting period
-- columns as Pacific Standard Time
SELECT BusinessEntityID,
    PersonType,
    NameStyle,
    Title,
    FirstName,
    MiddleName,
    ValidFrom AT TIME ZONE 'Pacific Standard Time'
FROM Person.Person_Temporal
FOR SYSTEM_TIME AS OF @ASOF;

Related content