Oracle 日期範圍查詢陷阱：TO_DATE 排除邊界 vs TO_CHAR 包含邊界

核心差異一覽

實戰對比

場景：查詢 2015-02-03 至 2015-02-06 的操作日志

-- 寫法 1：TO_DATE（不含 2 月 6 日當天數據）
SELECT * FROM syst_log
WHERE operate_time > TO_DATE('2015-02-03','YYYY-MM-DD')
  AND operate_time < TO_DATE('2015-02-06','YYYY-MM-DD')
ORDER BY operate_time DESC;

-- 寫法 2：TO_CHAR（含 2 月 3 日與 2 月 6 日全天）
SELECT * FROM syst_log
WHERE TO_CHAR(operate_time,'YYYY-MM-DD') BETWEEN '2015-02-03' AND '2015-02-06'
ORDER BY operate_time DESC;

根因深度解析

• TO_DATE('2015-02-06','YYYY-MM-DD') 實際值為 2015-02-06 00:00:00。條件 operate_time < 2015-02-06 00:00:00 將排除 2 月 6 日全天所有記錄（最早也是 00:00:00.000...）。
• TO_CHAR(operate_time,'YYYY-MM-DD') 將時間戳截斷為日期字符串，比較時等同於按「日」分組，自然包含邊界兩天的完整 24 小時。

最佳實踐建議

1. 業務報表/按日統計：優先使用 TO_CHAR 或 TRUNC(operate_time)，語義清晰、包含邊界。
2. 精確時間窗口：使用 TO_DATE 並顯式指定時分秒，如 operate_time >= TO_DATE('2015-02-03 00:00:00','YYYY-MM-DD HH24:MI:SS') AND operate_time < TO_DATE('2015-02-07 00:00:00','YYYY-MM-DD HH24:MI:SS')（結束日 +1 天）。
3. 索引友好寫法：避免在列上使用函數（TO_CHAR(col) 導致索引失效）。改用範圍條件：
   operate_time >= TO_DATE('2015-02-03','YYYY-MM-DD')
AND operate_time < TO_DATE('2015-02-07','YYYY-MM-DD') —— 可用索引、包含 2/3~2/6 全天、邏輯等價於 TO_CHAR 寫法。

性能補充

TO_CHAR 逐行轉換字符串比較，CPU 開銷大且阻礙索引範圍掃描。大表務必採用「顯式日期範圍 + 索引」模式。