Oracle日期SQL报错ORA-01858：预期数字处出现非数字字符

错误原因 #

ORA-01858的核心问题是日期转换过程中遇到了非预期的非数字字符，结合你的SQL语句，主要有两种可能：

• DATE字段误用TO_DATE转换：如果pa.actual_exp_date本身是DATE类型，使用TO_DATE(pa.actual_exp_date, 'YYYYMMDD')属于多余操作。Oracle会先将DATE类型字段自动转为字符串（遵循当前会话的NLS_DATE_FORMAT参数，比如DD-MON-RR格式会生成31-MAY-23这类带字母的字符串），再用YYYYMMDD格式解析该字符串，自然触发非数字字符错误。
• 字符串字段存在无效值：如果pa.actual_exp_date是VARCHAR2类型，说明字段中存在不符合YYYYMMDD格式的数据——比如带分隔符的2023/05/31、长度不足的202305、含字母的2023MAY31，甚至无效日期20230230，这些都会导致TO_DATE转换失败。

解决方法 #

情况1：字段是DATE类型（最常见场景） #

直接去掉对DATE字段的TO_DATE转换，同时优化WHERE条件的日期对比（用DATE类型直接对比，比字符串对比更高效且避免隐式转换）：

SELECT pa.actual_exp_date - TO_DATE('20230531', 'YYYYMMDD') AS out_days
FROM   pre_agrees pa
       JOIN pass p ON pa.pre_id = p.pass_id
WHERE  pa.prepaid_status = 'A'
AND    pa.actual_exp_date >= TO_DATE('20230531', 'YYYYMMDD')
AND    pa.actual_exp_date IS NOT NULL
AND    p.effective_date <= TO_DATE('20230531', 'YYYYMMDD');

情况2：字段是VARCHAR2类型 #

先过滤掉不符合YYYYMMDD格式的无效数据：

• Oracle 12c及以上版本，使用VALIDATE_CONVERSION函数判断转换可行性：

SELECT TO_DATE(pa.actual_exp_date, 'YYYYMMDD') - TO_DATE('20230531', 'YYYYMMDD') AS out_days
FROM   pre_agrees pa
       JOIN pass p ON pa.pre_id = p.pass_id
WHERE  pa.prepaid_status = 'A'
AND    VALIDATE_CONVERSION(pa.actual_exp_date AS DATE, 'YYYYMMDD') = 1
AND    pa.actual_exp_date >= '20230531'
AND    pa.actual_exp_date IS NOT NULL
AND    VALIDATE_CONVERSION(p.effective_date AS DATE, 'YYYYMMDD') = 1
AND    p.effective_date <= '20230531';

• Oracle 12c以下版本，用正则表达式过滤：

SELECT TO_DATE(pa.actual_exp_date, 'YYYYMMDD') - TO_DATE('20230531', 'YYYYMMDD') AS out_days
FROM   pre_agrees pa
       JOIN pass p ON pa.pre_id = p.pass_id
WHERE  pa.prepaid_status = 'A'
AND    REGEXP_LIKE(pa.actual_exp_date, '^[0-9]{8}$')
AND    pa.actual_exp_date >= '20230531'
AND    pa.actual_exp_date IS NOT NULL
AND    REGEXP_LIKE(p.effective_date, '^[0-9]{8}$')
AND    p.effective_date <= '20230531';

内容的提问来源于stack exchange，提问作者SATG123