Oracle ORA-01843/ORA-01861 日期格式錯誤徹底解析：NLS_DATE_FORMAT 會話參數影響與 TO_DATE 顯式格式化最佳實踐

兩大經典錯誤

• ORA-01861：字面值與格式字串不匹配（隱含轉換失敗）
• ORA-01843：無效月份（格式指定但解析失敗）

錯誤重現場景

-- 錯誤 1：隱含轉換格式不匹配
SELECT COUNT(*) FROM DBA_OBJECTS WHERE CREATED > TO_DATE('2008-12-01');
-- ORA-01861

-- 錯誤 2：指定格式但月份無效
SELECT COUNT(*) FROM DBA_OBJECTS WHERE CREATED > TO_DATE('19-11月-08','MM-DD-YYYY');
-- ORA-01843

正確用法示範

-- 會話 NLS_DATE_FORMAT 為 'DD-MON-RR' 時可直接比較
SELECT COUNT(*) FROM DBA_OBJECTS WHERE CREATED > '19-11月-08';
-- 正確返回 4199

-- 修改會話格式後原寫法失效
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT COUNT(*) FROM DBA_OBJECTS WHERE CREATED > '19-11月-08';
-- ORA-01861

-- 符合新格式則成功
SELECT COUNT(*) FROM DBA_OBJECTS WHERE CREATED > '2008-12-01';

核心參數查詢

SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF,
       SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') DL FROM DUAL;
-- DF: DD-MON-RR, DL: SIMPLIFIED CHINESE

-- 資料庫級固定參數
SELECT * FROM V$NLS_PARAMETERS;
-- NLS_DATE_FORMAT: DD-MON-RR
-- NLS_DATE_LANGUAGE: SIMPLIFIED CHINESE (月份顯示中文如「11月」)

最佳實踐鐵律

1. 永遠顯式指定 TO_DATE 格式：TO_DATE('2008-12-01', 'YYYY-MM-DD')
2. 測試請在 SQL*Plus 純環境進行，避免應用層自動修改會話參數干擾
3. 理解 NLS_DATE_LANGUAGE 影響月份名稱語言（中文環境月份為中文）