Oracle TO_DATE 格式模型不符導致錯誤的完整解析與避坑指南

TO_DATE(string, fmt) 與 TO_CHAR(date, fmt) 是 Oracle 日期處理的雙核心函式。最常見錯誤是「格式模型 fmt 與輸入字串不匹配」，導致 ORA-018xx 系列錯誤。本文系統整理格式元素、實戰錯誤案例、除錯技巧與最佳實務。

格式元素完整對照表

經典錯誤案例與修正

案例 1：小時制混用

-- 錯誤：輸入 13 點卻用 HH12
TO_DATE('2023-12-25 13:25:59', 'YYYY-MM-DD HH:MI:SS')
-- ORA-01847: day of month must be between 1 and last day of month (實際誤判為小時超界)

-- 正確：改用 HH24
TO_DATE('2023-12-25 13:25:59', 'YYYY-MM-DD HH24:MI:SS')

案例 2：分隔符號不符

TO_DATE('2023/12/25', 'YYYY-MM-DD')  -- 錯誤，分隔符 / vs -
TO_DATE('2023/12/25', 'YYYY/MM/DD')  -- 正確

案例 3：語言環境導致月份名稱不符

-- Session NLS_DATE_LANGUAGE = AMERICAN
TO_DATE('25-DEC-2023', 'DD-MON-YYYY')  -- 正常
-- 若 Session 為繁體中文，需 TO_DATE('25-12月-2023', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')

除錯三步驟

1. 拆解對比： 將輸入字串與格式模型逐字元對齊，檢查長度、分隔符、大小寫。
2. 使用 FX 修飾詞： TO_DATE(str, 'FXYYYY-MM-DD') 強制完全比對，若多一空白即報錯，利於開發期發現問題。
3. 檢查 Session 參數： SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER LIKE '%DATE%'; 確認 NLS_DATE_FORMAT、NLS_DATE_LANGUAGE 是否干擾隱含轉換。

最佳實務建議

• 程式碼中永遠顯式指定格式，不依賴 NLS_DATE_FORMAT 隱含轉換。
• 資料匯入前，以正規表示式驗證來源字串格式一致性。
• 跨時區系統改用 TIMESTAMP WITH TIME ZONE + TO_TIMESTAMP_TZ，避免 DATE 型態無時區資訊的缺陷。

掌握格式模型細節，善用 FX 與顯式轉換，即可杜絕 90% 以上的 TO_DATE 錯誤。