共用方式為

datetime2 (Transact-SQL)

  • 發行項

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 的 SQL 端點分析 Microsoft Fabric 的倉儲

定義日期，並結合了 24 小時制的時間。 datetime2 可視為現有 datetime 類型的延伸，因其具有較大的日期範圍、較大的預設小數有效位數和選擇性的使用者指定有效位數。

datetime2 描述

屬性 值
語法 datetime2 [ (毫秒精確度) ]
使用方式 DECLARE @MyDatetime2 datetime2(7);
CREATE TABLE Table1 (Column1 datetime2(7));
預設的字串常值格式

(用於下層用戶端)		 yyyy-MM-dd HH:mm:ss[.nnnnnnn]

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

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日
時間範圍 00:00:00 至 23:59:59.9999999
時區位移範圍 無
元素範圍 yyyy 是四位數的數字，範圍從 0001 到 9999，代表一年。

MM 是兩位數的數字，範圍從 01 到 12，代表指定年份中的月份。

dd 是兩位數的數字，範圍 01 從 到 31 ，視月份而定，代表指定月份的一天。

HH 是兩位數的數字，範圍從 00 到 23，代表小時。

mm 是兩位數的數字，範圍從 00 到 59，代表分鐘。

ss 是兩位數的數字，範圍從 00 到 59，代表第二個數位。

n* 是從 0 到 9999999的零到七位數數位，代表小數秒。 在 Informatica 中，當 n 小於 3時，小數秒會截斷。
字元長度 最低 19 個位置 （yyyy-MM-dd HH:mm:ss） 至 27 個最大值 （yyyy-MM-dd HH:mm:ss.0000000）
有效位數，小數位數 0 到 7 位數，精確度為 100 奈秒（100 ns）。 預設有效位數是 7 位數。

在 Microsoft Fabric 中，此精確度可以是 0 到 6 的整數，沒有預設值。 必須在 Microsoft Fabric 中指定有效位數。
儲存體大小 1 不到 3 個有效位數為 6 個位元組。
3 或4 個有效位數為 7 個位元組。

所有其他有效位數都需要8個字節。 2
精確度 100 奈秒
預設值 1900-01-01 00:00:00
Calendar 西曆
使用者自訂的小數秒數有效位數 Yes
時區位移感知和保留 否
日光節約感知 否

1 所提供值適用於未壓縮的資料列存放區。 使用數據壓縮或資料行存放區可能會改變每個精確度的記憶體大小。 此外，磁碟和記憶體中的記憶體大小可能會有所不同。 例如，使用批次模式時，datetime2 值在記憶體中一律需要 8 個位元組。

2 當 datetime2 值轉換成 varbinary 值時，會將額外的位元組新增至 varbinary 值以儲存有效位數。

如需數據類型元數據，請參閱 sys.systypes 或 TYPEPROPERTY。 有效位數和小數位數是某些日期和時間資料類型的變數。 若要取得數據行的有效位數和小數字數，請參閱 COLUMNPROPERTY、 COL_LENGTH或 sys.columns。

datetime2 支援的字串常值格式

下表列出 datetime2 支援的 ISO 8601 及 ODBC 字串常值格式。 如需 datetime2 日期和時間部分的字母、數值、未分隔及時間格式的相關信息，請參閱日期和時間。

ISO 8601 說明
yyyy-MM-ddTHH:mm:ss[.nnnnnnn] 此格式不會受到 SET LANGUAGE 和 SET DATEFORMAT 工作階段地區設定的影響。 、 T冒號 （:） 和句號 （.） 包含在字串常值中，例如 2024-05-02T19:58:47.1234567。
ODBC 描述
{ ts 'yyyy-MM-dd HH:mm:ss[.nnnnnnn]' } ODBC API 專用：

小數點右邊的位數 (代表小數秒數) 可指定為 0 至 7 (100 奈秒)。

ANSI 和 ISO 8601 合規性

date 和 time 的 ANSI 與 ISO 8601 合規性適用於 datetime2。

下層用戶端的回溯相容性

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

SQL Server 資料類型 傳遞至下層用戶端的預設字串常值格式 下層 ODBC 下層 OLEDB 下層 JDBC 下層 SQLCLIENT
time HH：mm：ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STR Java.sql.String 字串或 SqString
date yyyy-MM-dd SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STR Java.sql.String 字串或 SqString
datetime2 yyyy-MM-dd HH：mm：ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STR Java.sql.String 字串或 SqString
datetimeoffset yyyy-MM-dd HH：mm：ss[.nnnnnnn] [+|-]hh：mm SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STR Java.sql.String 字串或 SqString

轉換日期和時間數據

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

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

本節描述當其他日期與時間資料類型轉換成 datetime2 資料類型時，可能發生的狀況。

從 date 轉換時，年、月、日都會複製。 時間元件會設定為 00:00:00.0000000。 下列程式碼顯示將 date 值轉換成 datetime2 值的結果。

DECLARE @date AS DATE = '12-21-16';

DECLARE @datetime2 AS DATETIME2 = @date;

SELECT @datetime2 AS '@datetime2',
       @date AS '@date';

結果集如下所示。

@datetime2                  @date
--------------------------- ----------
2016-12-21 00:00:00.0000000 2016-12-21

從 time（n）轉換時，會複製時間元件，並將日期元件設定為 1900-01-01。 下列範例顯示將 time（7） 值轉換成 datetime2 值的結果。

DECLARE @time AS TIME (7) = '12:10:16.1234567';

DECLARE @datetime2 AS DATETIME2 = @time;

SELECT @datetime2 AS '@datetime2',
       @time AS '@time';

結果集如下所示。

@datetime2                  @time
--------------------------- ----------------
1900-01-01 12:10:16.1234567 12:10:16.1234567

從 smalldatetime 轉換時，會複製小時和分鐘。 秒和小數秒數會設定為 0。 下列程式碼顯示將 smalldatetime 值轉換成 datetime2 值的結果。

DECLARE @smalldatetime AS SMALLDATETIME = '12-01-16 12:32';

DECLARE @datetime2 AS DATETIME2 = @smalldatetime;

SELECT @datetime2 AS '@datetime2',
       @smalldatetime AS '@smalldatetime';

結果集如下所示。

@datetime2                  @smalldatetime
--------------------------- -----------------------
2016-12-01 12:32:00.0000000 2016-12-01 12:32:00

從 datetimeoffset(n) 轉換時，會複製日期和時間元件。 時區則會被截斷。 下列範例顯示將 datetimeoffset(7) 值轉換成 datetime2 值的結果。

DECLARE @datetimeoffset AS DATETIMEOFFSET (7) = '2016-10-23 12:45:37.1234567 +10:0';

DECLARE @datetime2 AS DATETIME2 = @datetimeoffset;

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

結果集如下所示。

@datetime2                  @datetimeoffset
--------------------------- ----------------------------------
2016-10-23 12:45:37.1234567 2016-10-23 12:45:37.1234567 +10:00

當轉換來自 datetime，會複製日期和時間。 小數有效位數會延伸至 7 位數。 下列範例顯示將 datetime 值轉換成 datetime2 值的結果。

DECLARE @datetime AS DATETIME = '2016-10-23 12:45:37.333';

DECLARE @datetime2 AS DATETIME2 = @datetime;

SELECT @datetime2 AS '@datetime2',
       @datetime AS '@datetime';

結果集如下所示。

@datetime2                  @datetime
----------------------- ---------------------------
2016-10-23 12:45:37.3333333 2016-10-23 12:45:37.333

使用 datetime 時明確轉換成 datetime2

在資料庫相容性層級 130 和更新版本下，從 datetime 到 datetime2 數據類型的隱含轉換會藉由考慮小數毫秒來提升精確度，進而產生不同的轉換值，如上一個範例所示。 每當 datetime 與 datetime2 數據類型之間存在混合比較案例時，請使用明確轉換成 datetime2 數據類型。 如需詳細資訊，請參閱 SQL Server 和 Azure SQL 資料庫 處理某些數據類型和罕見作業的改善。

將字串常值轉換為 datetime2

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

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

範例

下列範例會比較將字串轉換成各種 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';

結果集如下所示。

資料類型 輸出
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

相關內容