Oracle中VARCHAR2日期与SYSDATE日差计算WHERE条件报错问题

问题根因 #

你在SELECT中能得到正确结果，是因为返回的行都是格式符合YYYY-MM-DD HH24:MI:SS的有效数据；但WHERE子句会扫描全表所有行，一旦遇到格式无效的screen_entry_value值（比如长度不足19位、日期/时间部分非法），执行TO_DATE转换时就会触发ORA-01840错误。

解决方案 #

方案1：用VALIDATE_CONVERSION过滤无效数据（Oracle 12c及以上）

Oracle 12c提供的VALIDATE_CONVERSION函数可先判断字符串是否能转换为指定格式的日期，再执行后续条件：

SELECT TO_DATE(peevf.screen_entry_value, 'YYYY-MM-DD HH24:MI:SS') - TRUNC(SYSDATE) AS day_diff
FROM pay_element_entry_values_f peevf
WHERE VALIDATE_CONVERSION(peevf.screen_entry_value AS DATE, 'YYYY-MM-DD HH24:MI:SS') = 1
  AND TO_DATE(peevf.screen_entry_value, 'YYYY-MM-DD HH24:MI:SS') = TRUNC(SYSDATE + 80);

VALIDATE_CONVERSION返回1表示转换有效，0表示无效，先过滤掉无效行再做日期判断，即可避免报错。

方案2：自定义函数过滤无效数据（Oracle 12c以下）

如果你的Oracle版本低于12c，没有VALIDATE_CONVERSION，可以自定义一个函数校验日期格式：

CREATE OR REPLACE FUNCTION is_valid_date(p_str VARCHAR2, p_format VARCHAR2) RETURN NUMBER IS
    v_date DATE;
BEGIN
    v_date := TO_DATE(p_str, p_format);
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END;
/

然后用该函数过滤无效行：

SELECT TO_DATE(peevf.screen_entry_value, 'YYYY-MM-DD HH24:MI:SS') - TRUNC(SYSDATE) AS day_diff
FROM pay_element_entry_values_f peevf
WHERE is_valid_date(peevf.screen_entry_value, 'YYYY-MM-DD HH24:MI:SS') = 1
  AND TO_DATE(peevf.screen_entry_value, 'YYYY-MM-DD HH24:MI:SS') = TRUNC(SYSDATE + 80);

方案3：从根源解决——修改字段类型

长远来看，把screen_entry_value字段从VARCHAR2改成DATE类型才是最优解。用字符串存储日期本身就容易引入脏数据，后续查询、维护都会有隐患，改成DATE类型后无需转换，直接进行日期运算即可。

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