共用方式為

datetimeoffset (Transact-SQL)

  • 發行項

適用於：sql Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Platform System （PDW） SQL 分析端點在 Microsoft Fabric SQL 資料庫中的 Microsoft 網狀架構倉儲中Microsoft網狀架構

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

datetimeoffset 描述

屬性 值
語法 DATETIMEOFFSET [ （ 小數秒精確度 ） ]
使用方式 DECLARE @MyDatetimeoffset DATETIMEOFFSET(7);
CREATE TABLE Table1 (Column1 DATETIMEOFFSET(7));
預設字串常值格式 （用於下層用戶端） yyyy-MM-dd HH：mm：ss[.nnnnnnn] [{+|-}hh：mm]

如需詳細資訊，請參閱 後續下層用戶端 的回溯相容性一節。
日期範圍 0001-01-01 至 9999-12-31

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日
時間範圍 00:00:00 至 23:59:59.9999999
時區位移範圍 -14:00 至 +14:00
項目範圍 yyyy 是四位數，範圍從 0001 到 9999，代表一年。

MM 是兩位數，範圍從 01 到 12，代表指定年份中的月份。
dd 是兩位數，範圍 01 從 到 31 ，視月份而定，代表指定月份的一天。
HH 是兩位數，範圍從 00 到 23，代表小時。
mm 是兩位數，範圍從 00 到 59，代表分鐘。
ss 是兩位數，範圍從 00 到 59，代表第二個數位。
n 是零到七位數，範圍從 0 到 9999999，代表小數秒。
hh 是範圍從 -14 到 +14的兩位數。
mm 是範圍從 00 到 59的兩位數。
字元長度 最小 26 個位置 （yyyy-MM-dd HH：mm：ss {+|-}hh：mm） 到 34 最大值 （yyyy-MM-dd HH：mm：ss.nnnnnn {+|-}hh：mm）
精確度、小數位數 請參閱下表。
儲存體大小 10 個字節，固定是預設值，預設值為100-ns小數秒有效位數。
準確度 100 奈秒
預設值 1900-01-01 00:00:00 00:00
Calendar 西曆
用戶定義的小數秒有效位數 Yes
時區位移感知與保留 Yes
日光節約感知 否
指定的小數位數 結果 (有效位數，小數位數) 資料行長度 (以位元組為單位) 小數秒數有效位數
datetimeoffset (34, 7) 10 7
datetimeoffset(0) (26, 0) 8 0 到 2
datetimeoffset(1) (28, 1) 8 0 到 2
datetimeoffset(2) (29, 2) 8 0 到 2
datetimeoffset(3) (30, 3) 9 3 到 4
datetimeoffset(4) (31, 4) 9 3 到 4
datetimeoffset(5) (32, 5) 10 5 到 7
datetimeoffset(6) (33, 6) 10 5 到 7
datetimeoffset(7) (34, 7) 10 5 到 7

datetimeoffset 支援的字串常值格式

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

ISO 8601 描述
yyyy-MM-ddTHH：mm：ss[.nnnnnnn][{+|-}hh：mm] 這兩種格式不會受到 SET LANGUAGE 和 SET DATEFORMAT 會話地區設定的影響。 datetimeoffset 與 datetime 元件之間不允許空格。
yyyy-MM-ddTHH：mm：ss[.nnnnnnn]Z （UTC） 根據 ISO 定義，這種格式表示 datetime 部分應該以國際標準時間 (UTC) 來表示。 例如， 1999-12-12 12:30:30.12345 -07:00 應該表示為 1999-12-12 19:30:30.12345Z。

下列範例會比較將字串轉換成各種 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 12:35:29.1234567
date 2007-05-08
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29.1234567
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15
datetimeoffset IS08601 2007-05-08 12:35:29.1234567 +12:15

時區位移

時區位移指定 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 資料類型 傳遞至下層用戶端的預設字串常值格式 下層 ODBC 下層 OLEDB 下層 JDBC 下層 SQLCLIENT
time HH：mm：ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTRor DBTYPE_STR Java.sql.String String 或 SqString
date yyyy-MM-dd SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTRor DBTYPE_STR Java.sql.String String 或 SqString
datetime2 yyyy-MM-dd HH：mm：ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTRor DBTYPE_STR Java.sql.String String 或 SqString
datetimeoffset yyyy-MM-dd HH：mm：ss[.nnnnnnn] [+|-]hh：mm SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTRor DBTYPE_STR Java.sql.String String 或 SqString

Microsoft網狀架構支援

在 Microsoft Fabric 中，目前您無法使用 datetimeoffset 數據類型建立數據行，但您可以使用 datetimeoffset 搭配 AT TIME ZONE （Transact-SQL） 函式來轉換數據，例如：

SELECT
CAST(CAST('2024-07-03 00:00:00' AS DATETIMEOFFSET) AT TIME ZONE 'Pacific Standard Time' AS datetime2) AS PST

在 Microsoft Fabric SQL 資料庫中：可以使用 7 位數的有效位數，但 Fabric OneLake 中的鏡像數據會具有時區和第七次修剪小數點。 這個數據行類型不能當做 Fabric SQL 資料庫中數據表的主鍵使用。

轉換日期和時間數據

當您轉換成日期和時間數據類型時，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 資料類型的規則。

輸入字串常值 datetimeoffset（n）
ODBC DATE ODBC 字串常值會對應到 datetime 資料類型。 從ODBC DATETIME常值到 datetimeoffset 類型的任何指派作業都會在 datetime 和此類型之間產生隱含轉換，如轉換規則所定義。
ODBC TIME 請參閱上一個 ODBC DATE 規則
ODBC DATETIME 請參閱上一個 ODBC DATE 規則
僅限 DATE 元件 TIME 預設為 00:00:00。 預設值 TIMEZONE 為 +00:00
僅限 TIME 元件 DATE 預設為 1900-1-1。 預設值 TIMEZONE 為 +00:00
僅限 TIMEZONE 提供預設值
DATE + TIME 預設值 TIMEZONE 為 +00:00
DATE + TIMEZONE 不允許
TIME + TIMEZONE DATE 元件預設為 1900-1-1
DATE + TIME + TIMEZONE 一般

相關內容