Oracle 判斷字符型數據能否轉換為合法日期（解決 ORA-01847/ORA-01843）

問題背景

用戶表 TM_WWM 中日期字段 SALEDATE 為 VARCHAR2(8)，格式 YYYYMMDD。導入前需驗證日期合法性，直接 TO_DATE 會拋出錯誤：

• ORA-01847: day of month must be between 1 and last day of month（如 00 日、2 月 30 日）
• ORA-01843: not a valid month（月份非 01-12）

解決方案：PL/SQL 函數驗證日期合法性

創建一個函數，捕獲轉換異常，返回標誌位或合法日期：

CREATE OR REPLACE FUNCTION is_valid_date(p_str VARCHAR2, p_fmt VARCHAR2 DEFAULT 'YYYYMMDD')
RETURN NUMBER IS
  v_date DATE;
BEGIN
  v_date := TO_DATE(p_str, p_fmt);
  RETURN 1;  -- 合法
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;  -- 不合法
END;
/

使用示例

-- 查詢所有非法日期記錄
SELECT * FROM TM_WWM
WHERE is_valid_date(SALEDATE) = 0;

-- 僅導入合法日期數據
INSERT INTO target_table (sale_date)
SELECT TO_DATE(SALEDATE, 'YYYYMMDD')
FROM TM_WWM
WHERE is_valid_date(SALEDATE) = 1;

進階：純 SQL 正則表達式預檢（Oracle 10g+）

在調用 TO_DATE 前先用正則過濾明顯格式錯誤，減少異常開銷：

SELECT * FROM TM_WWM
WHERE REGEXP_LIKE(SALEDATE, '^\d{8}$')  -- 8 位數字
  AND TO_NUMBER(SUBSTR(SALEDATE, 5, 2)) BETWEEN 1 AND 12  -- 月份 01-12
  AND TO_NUMBER(SUBSTR(SALEDATE, 7, 2)) BETWEEN 1 AND 31;  -- 日期 01-31（粗略）
-- 再配合 is_valid_date 函數做精確校驗

最佳實踐建議

1. 源頭治理：在數據錄入/ETL 階段強制使用 DATE 類型或加強前端校驗，避免髒數據入庫。
2. 批量驗證：大數據量時，先用正則快速過濾，再用 PL/SQL 函數精確驗證，平衡性能與準確性。
3. 記錄異常：將非法日期記錄寫入異常表，便於後續人工修正或追溯。