核心差異對照表
| 特性 | datetime | smalldatetime | date (SQL 2008+) |
|---|
| 日期範圍 | 1753-01-01 ~ 9999-12-31 | 1900-01-01 ~ 2079-06-06 | 0001-01-01 ~ 9999-12-31 |
| 時間元件 | 有 (時分秒毫秒) | 有 (時分) | 無 (僅日期) |
| 精度 | 3.33 毫秒 | 1 分鐘 | 1 天 |
| 儲存大小 | 8 bytes | 4 bytes | 3 bytes |
| 零值顯示 | 1900-01-01 00:00:00.000 | 1900-01-01 00:00 | 0001-01-01 |
| 適用版本 | 所有版本 | 所有版本 | SQL Server 2008+ |
精度行為細節
- datetime：實際儲存精度為 1/300 秒 (3.33ms)，數值會四捨五入到 .000, .003, .007 秒
- smalldatetime：秒數 ≥ 29.999 進位到下一分鐘，< 29.999 捨去到本分鐘
- date：無時間元件，比較/排序最直觀
讀取顯示陷阱
「讀取該資料時不管你的欄位是 datetime 或 smalldatetime 讀出來的格式都一樣 (如：1900-01-01)。datetime 不會顯示出他的毫秒，但是在進行時間比較時他要毫秒就會出現，從而讓兩個時間段不能相等。」
-- 範例：視覺相同但比較不同
DECLARE @dt DATETIME = '2023-01-01 12:00:00.000';
DECLARE @sdt SMALLDATETIME = '2023-01-01 12:00';
SELECT @dt, @sdt; -- 顯示可能相同
SELECT CASE WHEN @dt = @sdt THEN '相等' ELSE '不等' END; -- 可能不等 (毫秒差異)
新開發選型建議
| 場景 | 推薦類型 | 理由 |
|---|
| 僅需日期 (生日、生效日、到期日) | date | 節省空間、語義明確、無時間干擾 |
| 需日期+時間、高精度 (交易時間、日誌) | datetime2(n) | 100ns 精度、範圍更大、取代 datetime |
| 需時區感知 (全球化應用) | datetimeoffset(n) | 含時區位移 |
| 舊系統相容、不需高精度 | datetime | 相容性考量 |
| 極度節省空間、精度要求低 | smalldatetime | 4 bytes，但範圍受限 |
遷移建議
- 舊表
datetime 存純日期 → 改為 date (需驗證應用層相容)
- 新增欄位優先用
date/datetime2
- 比較邏輯統一用
CAST(... AS DATE) 忽略時間元件
