Oracle WHERE子句处理Varchar时间字段遇ORA-01840错误求助

错误原因 #

你的starttime字段存储的是14位无分隔符的字符串（格式为YYYYMMDDHH24MISS，例如20211110200336），但执行TO_DATE(starttime,'YYYY-MM-DD HH24:MI:SS')时使用的格式模板包含分隔符（-和:），导致Oracle解析时字符串长度与模板要求不匹配，触发ORA-01840错误。

解决方案 #

方案1：直接截取字符串判断（性能最优）

利用字符串固定格式的特性，直接提取时间部分进行比较，无需转换日期：

SELECT *
FROM 你的表名
WHERE LENGTH(starttime) = 14 -- 过滤无效格式数据
  AND SUBSTR(starttime, 9, 6) BETWEEN '000000' AND '050000';

如果只需要判断小时范围，可简化为：

SELECT *
FROM 你的表名
WHERE LENGTH(starttime) = 14
  AND TO_NUMBER(SUBSTR(starttime, 9, 2)) BETWEEN 0 AND 5;

方案2：使用正确的日期转换格式

将TO_DATE的格式模板改为与存储格式匹配的YYYYMMDDHH24MISS，再提取时间部分判断：

SELECT *
FROM 你的表名
WHERE LENGTH(starttime) = 14
  AND TO_CHAR(TO_DATE(starttime, 'YYYYMMDDHH24MISS'), 'HH24:MI:SS') BETWEEN '00:00:00' AND '05:00:00';

额外建议 #

如果starttime字段存在非14位的无效数据，建议先清理这类数据，或者在查询时通过LENGTH(starttime)=14过滤，避免转换或截取时出现额外错误。

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