您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 日期時間類型深度解析：DATETIME 精度陷阱、十六進制儲存結構與 SMALLDATETIME 差異

編輯：關於SqlServer

SQL Server 日期時間類型 (`DATETIME`, `SMALLDATETIME`, `DATE`, `TIME`, `DATETIME2`, `DATETIMEOFFSET`) 在精度、儲存大小、範圍上差異巨大。本文聚焦舊版核心類型 `DATETIME` (3.33ms 精度) 與 `SMALLDATETIME` (1分鐘精度) 的**精度陷阱**、**二進制儲存結構**，透過實測腳本揭示隱性轉換行為，避免查詢比較、索引搜尋、ETL 匯入出現難以排查的 Bug。

一、 DATETIME 精度陷阱：3.33ms 圓整規則

`DATETIME` 實際精度為 **3.33 毫秒** (1/300 秒)，毫秒部分只能是 **0, 3, 7** (0.000, 0.003, 0.007)。輸入其他毫秒值會**自動圓整**。

實測腳本與結果


DECLARE @t TABLE (input_str CHAR(21));
INSERT @t VALUES ('1900-1-1 00:00:00.000');
INSERT @t VALUES ('1900-1-1 00:00:00.001');
INSERT @t VALUES ('1900-1-1 00:00:00.002');
INSERT @t VALUES ('1900-1-1 00:00:00.003');
INSERT @t VALUES ('1900-1-1 00:00:00.004');
INSERT @t VALUES ('1900-1-1 00:00:00.005');
INSERT @t VALUES ('1900-1-1 00:00:00.006');
INSERT @t VALUES ('1900-1-1 00:00:00.007');
INSERT @t VALUES ('1900-1-1 00:00:00.008');
INSERT @t VALUES ('1900-1-1 00:00:00.009');

SELECT input_str AS [輸入字串],
       CAST(input_str AS DATETIME) AS [轉換後 DATETIME],
       CAST(CAST(input_str AS DATETIME) AS BINARY(8)) AS [十六進制儲存]
FROM @t;

輸出結果 (關鍵觀察：.001→.000, .002→.003, .004→.003, .005→.007, .008→.007, .009→.010)


輸入字串                 轉換後 DATETIME           十六進制儲存
-----------------------  -----------------------   ----------------
1900-1-1 00:00:00.000    1900-01-01 00:00:00.000   0x0000000000000000
1900-1-1 00:00:00.001    1900-01-01 00:00:00.000   0x0000000000000000  ← 圓整到 0
1900-1-1 00:00:00.002    1900-01-01 00:00:00.003   0x0000000000000001  ← 圓整到 3
1900-1-1 00:00:00.003    1900-01-01 00:00:00.003   0x0000000000000001
1900-1-1 00:00:00.004    1900-01-01 00:00:00.003   0x0000000000000001  ← 圓整到 3
1900-1-1 00:00:00.005    1900-01-01 00:00:00.007   0x0000000000000002  ← 圓整到 7
1900-1-1 00:00:00.006    1900-01-01 00:00:00.007   0x0000000000000002
1900-1-1 00:00:00.007    1900-01-01 00:00:00.007   0x0000000000000002
1900-1-1 00:00:00.008    1900-01-01 00:00:00.007   0x0000000000000002  ← 圓整到 7
1900-1-1 00:00:00.009    1900-01-01 00:00:00.010   0x0000000000000003  ← 進位到 10ms

二、二進制儲存結構解析 (Little-Endian)

`DATETIME` = 8 Bytes = **4 Bytes 日期 (相對 1900-01-01 天數)** + **4 Bytes 時間 (相對午夜 00:00:00.000 的 3.33ms 刻度數)**。

實測驗證


-- 純日期：1900-01-02 (第 1 天)
DECLARE @dt DATETIME = '1900-1-2';
SELECT CAST(@dt AS BINARY(8)); -- 0x00000001 00000000 → 日期=1, 時間=0

-- 純時間：00:00:01 (1秒 = 300 刻度)
SET @dt = '00:00:01';
SELECT CAST(@dt AS BINARY(8)); -- 0x00000000 0000012C → 日期=0, 時間=0x12C (300)

SMALLDATETIME (4 Bytes = 2 Bytes 日期 + 2 Bytes 分鐘數)


DECLARE @sdt SMALLDATETIME;

SET @sdt = '1900-1-2';   -- 第 1 天
SELECT CAST(@sdt AS BINARY(4)); -- 0x0001 0000 → 日期=1, 時間=0

SET @sdt = '00:10';      -- 10 分鐘
SELECT CAST(@sdt AS BINARY(4)); -- 0x0000 000A → 日期=0, 時間=10 (0x0A)

三、類型選擇指南 (SQL Server 2008+ 新類型優先)

類型	儲存	精度	範圍	建議用途
DATETIME2(n)	6-8 Bytes	100ns (n=7)	0001-01-01 ~ 9999-12-31	新專案預設選擇，精度可配置，符合 ANSI SQL。
DATE	3 Bytes	天	同 DATETIME2	僅需日期 (生日、生效日)，省空間、比較快。
TIME(n)	3-5 Bytes	100ns	00:00:00 ~ 23:59:59.9999999	僅需時間 (班次、營業時間)。
DATETIMEOFFSET(n)	8-10 Bytes	100ns + 時區	含時區位移	跨時區應用、全球化系統。
DATETIME	8 Bytes	3.33ms	1753-01-01 ~ 9999-12-31	舊系統相容，新開發禁用。
SMALLDATETIME	4 Bytes	1 分鐘	1900-01-01 ~ 2079-06-06	極少用，精度太粗。

四、實務避坑指南

比較陷阱：`WHERE CreatedAt = '2023-01-01 12:00:00.001'` 永無匹配 (圓整後不等)。改用範圍：`WHERE CreatedAt >= '2023-01-01 12:00:00.000' AND CreatedAt < '2023-01-01 12:00:00.003'`。
ETL 匯入：來源系統毫秒精度高於 3.33ms → 匯入前決定捨去/圓整策略，記錄轉換日誌。
索引搜尋：精度圓整導致 `SEEK` 範圍變大，可能退化為 `SCAN`。新類型 `DATETIME2` 無此問題。
隱性轉換：`VARCHAR` 比較 `DATETIME` 依語言/格式設定解析，**永遠使用明確 `CAST`/`CONVERT` 或參數化查詢**。