datetimeoffset (Transact-SQL)

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

根據 datetime2 等 24 小時制，定義與一天時間結合的日期，並根據國際標準時間 （UTC） 新增時區感知。

datetimeoffset 描述

datetimeoffset 支援的字串常值格式

下表將列出 datetimeoffset 支援的 ISO 8601 字串常值格式。 如需 datetimeoffset 日期和時間部分的字母、數值、未分隔及時間格式的相關信息，請參閱 date （Transact-SQL） 和時間 （Transact-SQL）。

下列範例會比較將字串轉換成各種 date 和 time 資料類型的結果。

SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS TIME(7)) AS 'time',
    CAST('2007-05-08 12:35:29. 1234567 +12:15' AS DATE) AS 'date',
    CAST('2007-05-08 12:35:29.123' AS SMALLDATETIME) AS 'smalldatetime',
    CAST('2007-05-08 12:35:29.123' AS DATETIME) AS 'datetime',
    CAST('2007-05-08 12:35:29.1234567+12:15' AS DATETIME2(7)) AS 'datetime2',
    CAST('2007-05-08 12:35:29.1234567 +12:15' AS DATETIMEOFFSET(7)) AS 'datetimeoffset',
    CAST('2007-05-08 12:35:29.1234567+12:15' AS DATETIMEOFFSET(7)) AS 'datetimeoffset IS08601';

以下為結果集。

時區位移

時區位移指定 time 或 datetime 值從 UTC 算起的時區位移。 時區位移可表示成 [+|-] hh:mm：

• hh是介於和 14 之間的兩位數，00代表時區位移中的時數。

• mm 是兩位數，範圍從 00 到 59，代表時區位移中額外分鐘數。

• + （加號） 或 - （減號） 是時區位移的必要標誌。 此符號指出時區位移是從UTC時間新增或減去，以取得當地時間。 時區位移的有效範圍是從 -14:00 到 +14:00。

時區位移範圍遵循 XSD 架構定義的 W3C XML 標準，而且與 SQL 2003 標準定義稍有不同， 12:59 與 +14:00。

選擇性的類型參數「毫秒精確度」會指定秒鐘小數部分的位數。 這個值可以是介於 0 至 7 (100 奈秒) 之間的整數。 默認 的小數秒精確度 為100 ns（秒小數部分的七位數）。

這項資料會儲存於資料庫中，而且在伺服器中進行處理、比較、儲存和索引 (如同 UTC)。 時區位移會保留於資料庫中以供擷取。

指定時區位移假設為日光節約時間 （DST） 感知，並針對 DST 期間中的任何指定 日期時間 進行調整。

對於 datetimeoffset 類型，UTC 和 local （持續或轉換的時區位移） 日期時間 值會在插入、更新、算術、轉換或指派作業期間進行驗證。 偵測到任何無效的 UTC 或本機 （持續或轉換的時區位移） 日期時間 值會引發無效的值錯誤。 例如， 9999-12-31 10:10:00 在 UTC 中有效，但本地時間溢位至時區位移 +13:50。

時區轉換語法

SQL Server 2016 （13.x） 引進 AT TIME ZONE 了語法，以促進日光節約感知的通用時區轉換。 將沒有時區位移的數據轉換成具有時區位移的數據時，此語法特別有用。 若要轉換成目標時區中的對應 datetimeoffset 值，請參閱 AT TIME ZONE。

ANSI 和 ISO 8601 合規性

日期和時間發行項的 ANSI 和 ISO 8601 合規性區段適用於 datetimeoffset。

下層用戶端的回溯相容性

有些下層用戶端不支援 time、date、datetime2 及 datetimeoffset 資料類型。 下表顯示上層 SQL Server 執行個體與下層用戶端之間的類型對應。

轉換日期和時間數據

當您轉換成日期和時間數據類型時，SQL Server 會拒絕無法辨識為日期或時間的所有值。 如需搭配日期和時間數據使用 CAST 和 CONVERT 函式的相關信息，請參閱 CAST 和 CONVERT。

轉換成 datetimeoffset 數據類型

本節提供從數據類型更新數據的範例，而不需要位移至新的 datetimeoffset 數據類型數據行。

首先，從 系統目錄檢視sys.time_zone_info 確認時區名稱。

SELECT * FROM sys.time_zone_info WHERE name = 'Pacific Standard Time';

下列範例使用 AT TIME ZONE 語法兩次。 範例程式代碼會建立數據表 dbo.Audit、新增跨越多個日光節約時間變更的數據，並新增新的 datetimeoffset 數據行。 我們假設數據 AuditCreated 行是 *datetime2 數據類型，不含位移，並使用 UTC 時區撰寫。

在語句中UPDATEAT TIME ZONE，語法會先將 UTC 時區位移新增至現有的AuditCreated數據行數據，然後將數據從 UTC Pacific Standard Time轉換成 ，以正確調整過去日光節約時間範圍中每個過去日光節約時間範圍 美國 的歷史數據。

CREATE TABLE dbo.Audit (AuditCreated DATETIME2(0) NOT NULL);
GO

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('1/1/2024 12:00:00');

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('5/1/2024 12:00:00');

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('12/1/2024 12:00:00');
GO

ALTER TABLE dbo.Audit
ADD AuditCreatedOffset DATETIMEOFFSET(0) NULL;
GO

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]
FROM sys.time_zone_info
WHERE [name] = 'Pacific Standard Time';

UPDATE dbo.Audit
SET AuditCreatedOffset = AuditCreated
    AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone
WHERE AuditCreatedOffset IS NULL;
GO

SELECT *
FROM dbo.Audit;

以下為結果集。

AuditCreated         AuditCreatedOffset
-------------------  --------------------------
2024-01-01 12:00:00  2024-01-01 04:00:00 -08:00
2024-05-01 12:00:00  2024-05-01 05:00:00 -07:00
2024-12-01 12:00:00  2024-12-01 04:00:00 -08:00

將 datetimeoffset 資料類型轉換為其他日期和時間類型

本節說明當 datetimeoffset 資料類型轉換成其他日期和時間資料類型時，可能發生的狀況。

當您轉換成 日期時，會複製年份、月和日。 下列程式代碼顯示將 datetimeoffset（4） 值轉換成日期值的結果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date DATE = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset', @date AS 'date';

以下為結果集。

@datetimeoffset                 date
------------------------------ ----------
2025-12-10 12:32:10.0000 +01:0 2025-12-10

如果轉換是 time（n），則會複製小時、分鐘、秒和小數秒。 時區值則會被截斷。 當 datetimeoffset（n） 值的精確度大於 time（n） 值的精確度時，值會四捨五入。 下列程式代碼顯示將 datetimeoffset（4） 值轉換成 time（3） 值的結果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10.1237 +01:0';
DECLARE @time TIME(3) = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';

以下為結果集。

@datetimeoffset                 time
------------------------------- ------------
2025-12-10 12:32:10.1237 +01:00 12:32:10.124

當您轉換成 datetime 時，會複製日期和時間值，並截斷時區。 當 datetimeoffset（n） 值的分數有效位數大於三位數時，就會截斷該值。 下列程式代碼顯示將 datetimeoffset（4） 值轉換成 datetime 值的結果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10.1237 +01:0';
DECLARE @datetime DATETIME = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';

以下為結果集。

@datetimeoffset                datetime
------------------------------ -----------------------
2025-12-10 12:32:10.1237 +01:0 2025-12-10 12:32:10.123

當轉換成 smalldatetime 時，會複製日期和小時。 分鐘會根據秒值四捨五入，而秒會設定為 0。 下列程式代碼顯示將 datetimeoffset（3） 值轉換成 smalldatetime 值的結果。

DECLARE @datetimeoffset DATETIMEOFFSET(3) = '1912-10-25 12:24:32 +10:0';
DECLARE @smalldatetime SMALLDATETIME = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';

以下為結果集。

@datetimeoffset                @smalldatetime
------------------------------ -----------------------
1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00

如果轉換為 datetime2（n），則會將日期和時間複製到 datetime2 值，並截斷時區。 當 datetime2（n） 值的精確度大於 datetimeoffset（n） 值的精確度時，小數秒會截斷以符合。 下列程式代碼顯示將 datetimeoffset（4） 值轉換成 datetime2（3） 值的結果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '1912-10-25 12:24:32.1277 +10:0';
DECLARE @datetime2 DATETIME2(3) = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';

以下為結果集。

@datetimeoffset                    @datetime2
---------------------------------- ----------------------
1912-10-25 12:24:32.1277 +10:00    1912-10-25 12:24:32.12

將字串常值轉換為 datetimeoffset

如果整個字串皆是有效的格式，即可從字串常值轉換為日期與時間類型。 否則，就會引發執行階段錯誤。 不指定樣式的隱含轉換或明確轉換，從日期和時間類型到字串常值都是目前會話的預設格式。 下表是字串常值轉換為 datetimeoffset 資料類型的規則。

相關內容

• CAST 和 CONVERT (Transact-SQL)
• AT TIME ZONE (Transact-SQL)