淺談 SQL Server 日期時間查詢與精度進位陷阱
TLDR
- 應全面棄用
datetime與smalldatetime，改用datetime2作為標準時間型別，以避免精度進位與範圍限制問題。 - 建議明確指定資料庫欄位精度（如
datetime2(0)或datetime2(3)），避免依賴框架預設值導致的遷移風險。 - 進行時間區間查詢時，應一律採用
[開始時間] <= x < [結束時間]的寫法，嚴禁使用AddTicks(-1)進行閉合區間處理。 - 應用程式層應統一時間來源，避免在 SQL 語句中混用
GETDATE()或進行複雜的時間運算，以確保 SARGability 並維持索引效能。 - 當應用程式變數型別與資料庫欄位型別不一致時（如
datetime2變數查詢datetime欄位），會觸發隱式轉換導致效能下降或查詢結果異常。
SQL Server 的日期時間型別選擇
在 SQL Server 2025 環境下，不同日期時間型別的特性如下表所示：
| 型別 | 精度 | 說明 |
|---|---|---|
| datetime | 3.33 毫秒 | 舊系統產物，毫秒非連續，會自動進位。 |
| smalldatetime | 1 分鐘 | 秒數會四捨五入進位至分，範圍僅至 2079 年。 |
| datetime2 | 100 奈秒 | 新專案首選，精度高且範圍廣（0001-9999 年）。 |
| datetimeoffset | 100 奈秒 | 包含時區偏移量，適合跨國應用。 |
TIP
實務上，應優先選用 datetime2 或 datetimeoffset。smalldatetime 存在潛在的「千年蟲」風險，而 datetime 則因精度進位問題，已不建議在新開發中使用。此外，若需從 Oracle 遷移資料，datetime2 支援至 0001 年，能有效解決 datetime 僅支援至 1753 年導致的寫入失敗問題。
關於精度的選擇 (0)、(3)、(7)
- 精度
(0)(秒)：適用於不需要高精度顯示的場景，可避免前端顯示與後端儲存不一致導致的查詢失敗。 - 精度
(3)(毫秒)：業界通用標準，兼顧效能與解析度，適用於大多數高併發場景。 - 精度
(7)(100 奈秒)：與 C#DateTime.Ticks一致，適用於科學運算或系統日誌。
WARNING
若使用 Entity Framework Code First，請務必顯式指定精度（例如 .HasPrecision(0)），切勿依賴框架預設值，以免未來升級或遷移時發生非預期的行為。
時間來源的統一性與效能考量
當系統採用高精度（如 (7)）時，必須解決時間來源不一致的問題。若在應用程式多處重複呼叫 DateTime.Now，會因微小的時間差導致同一交易內的時間戳記不一致。
應用層 vs 資料庫層
建議將時間處理統一在應用程式層，並透過參數傳遞至資料庫。
- 什麼情況下會遇到問題：當 SQL 語句中直接使用
GETDATE()或對欄位進行運算（如DATEADD(MINUTE, -1, RecordTime) = GETDATE()）時，會導致 SARGability 失效，使索引搜尋（Index Seek）退化為索引掃描（Index Scan），嚴重影響效能。
csharp
// 建議寫法：在應用層取得時間後，作為參數傳入
DateTime now = DateTime.Now;
db.Table.Where(x => x.RecordTime == now);時間區間的閉合處理陷阱
在處理時間區間查詢時，常見的錯誤作法是使用 AddTicks(-1) 來處理結束時間的閉合。
- 什麼情況下會遇到問題：當資料庫欄位型別為
datetime，但應用程式變數為datetime2時，因兩者精度不同，AddTicks(-1)的操作會產生進位誤差，導致查詢結果遺漏或包含非預期的資料。
推薦做法
應一律使用「左閉右開」的區間查詢，以確保語意清晰且具備通用性：
csharp
// ✅ 推薦寫法
db.Table.Where(x => x.Time >= input.StartTime && x.Time < input.EndTime);精度進位導致的查詢異常
datetime 型別會將毫秒進位至 0、3、7。若宣告一個 datetime 變數為 '2026-02-10 07:59:59.999'，SQL Server 會將其進位為 '2026-02-10 08:00:00.000'，導致查詢條件與實際儲存值產生偏差。
如上圖所示，當使用 <= 比較時，型別不一致會導致執行計畫出現顯著差異，進而影響查詢效能與正確性。
異動歷程
- 2026-02-12 初版文件建立。