AT TIME ZONE (Transact-SQL)AT TIME ZONE (Transact-SQL)

適用於: 是SQL Server (從 2016 開始) 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

inputdate 轉換成目標時區中對應的 datetimeoffset 值。Converts an inputdate to the corresponding datetimeoffset value in the target time zone. 提供 inputdate 但未提供位移資訊時,此函式就會在假設 inputdate 位於目標時區的情況下,套用時區位移。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. 如果提供 inputdate 來作為 datetimeoffset 值,則 AT 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 實作需倚賴 Windows 機制來跨時區轉換 datetime 值。AT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

inputdate AT TIME ZONE timezone  

引數Arguments

inputdateinputdate
這是可解析成下列值的運算式:smalldatetimedatetimedatetime2datetimeoffsetIs an expression that can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.

timezonetimezone
目的地時區的名稱。Name of the destination time zone. SQL ServerSQL Server 需倚賴儲存在「Windows 登錄」中的時區。relies on time zones that are stored in the Windows Registry. 安裝在電腦上的所有時區都儲存在下列登錄區中:KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time ZonesTime zones installed on the computer are stored in the following registry hive: KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. 已安裝的時區清單也會透過 sys.time_zone_info (Transact-SQL) 檢視表公開。A list of installed time zones is also exposed through the sys.time_zone_info (Transact-SQL) view.

傳回類型Return Types

傳回 datetimeoffset 的資料類型。Returns the data type of datetimeoffset.

傳回值Return Value

目標時區中的 datetimeoffset 值。The datetimeoffset value in the target time zone.

RemarksRemarks

AT TIME ZONE 會針對 smalldatetimedatetimedatetime2 資料類型中,落在受 DST 變更影響之間隔內的輸入值,套用特定的轉換規則: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. 這段持續時間通常是 1 個小時,但也可能是 30 或 45 分鐘,視時區而定。This duration is usually 1 hour, but it can be 30 or 45 minutes, depending on time zone. 將會使用在 DST 變更「後」之位移來轉換位於此落差中的時間點。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 29th, 2015 at 02:00:00.   
        Adjusted local time became 2015-03-29 03:00:00.  
    */  
    
    --Time before DST change has standard time offset (+01:00)
    SELECT CONVERT(datetime2(0), '2015-03-29T01:01:00', 126)     
    AT TIME ZONE 'Central European Standard Time';  
    --Result: 2015-03-29 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), '2015-03-29T02:01:00', 126)   
    AT TIME ZONE 'Central European Standard Time';  
    --Result: 2015-03-29 03:01:00 +02:00
    
    --Time after 03:00 is presented with the summer time offset (+02:00)
    SELECT CONVERT(datetime2(0), '2015-03-29T03:01:00', 126)   
    AT TIME ZONE 'Central European Standard Time';  
    --Result: 2015-03-29 03:01:00 +02:00  
    
    
  • 將時鐘調整回來時,2 個小時的本地時間就會重疊成 1 個小時。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 25th, 2015 at 03:00:00.   
        Adjusted local time became 2015-10-25 02:00:00   
    */  
    
    --Time before the change has DST offset (+02:00)
    SELECT CONVERT(datetime2(0), '2015-10-25T01:01:00', 126)      
    AT TIME ZONE 'Central European Standard Time';  
    --Result: 2015-10-25 01:01:00 +02:00  
    
    /*
      Time from the "overlapped interval" is presented with standard time 
      offset (before the change)    
    */
    SELECT CONVERT(datetime2(0), '2015-10-25T02:00:00', 126)   
    AT TIME ZONE 'Central European Standard Time';  
    --Result: 2015-10-25 02:00:00 +02:00  
    
    
    --Time after 03:00 is regularly presented with the standard time offset (+01:00)    
    SELECT CONVERT(datetime2(0), '2015-10-25T03:01:00', 126)   
    AT TIME ZONE 'Central European Standard Time';  
    --Result: 2015-10-25 03:01:00 +01:00
    
    

由於部分資訊 (例如時區規則) 的維護是在 SQL ServerSQL Server 外部進行且可能偶爾會有變更,因此 AT TIME ZONE 函數被歸類為不具決定性的函數。Since some information (such as timezone rules) is maintained outside of SQL ServerSQL Server and are subject to occasional change, the AT TIME ZONE function is classed as nondeterministic.

範例Examples

A.A. 將目標時區位移新增至不含位移資訊的日期時間Add target time zone offset to datetime without offset information

當您知道在相同時區中已提供原始 datetime 值時,請使用 AT TIME ZONE 來根據時區規則新增位移: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 AdventureWorks2016;  
GO  
  
SELECT SalesOrderID, OrderDate,   
    OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST  
FROM Sales.SalesOrderHeader;  

B.B. 在不同的時區之間轉換值Convert values between different time zones

下列範例會在不同的時區之間轉換值:The following example converts values between different time zones:

USE AdventureWorks2016;  
GO  
  
SELECT SalesOrderID, OrderDate,   
    OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST,  
    OrderDate AT TIME ZONE 'Central European Standard Time' AS OrderDate_TimeZoneCET  
FROM Sales.SalesOrderHeader;  

C.C. 使用本地時區的查詢時態表Query Temporal Tables using local time zone

下列範例會從時態表選取資料。The following example selects data from a temporal table.

USE AdventureWorks2016;  
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;  

另請參閱See Also

日期和時間類型 Date and Time Types
日期和時間資料類型與函數 (Transact-SQL)Date and Time Data Types and Functions (Transact-SQL)