Cause: java.sql.SQLDataException: ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0

报错信息：

### The error may exist in file [E:\IdeaProjects\FeiShuEHR\target\classes\mapper\ZzzHrOaQingJiaMapper.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT DANHAO, A0190, LEAVE_TYPEID, LEAVE_TYPE, LEAVE_BEGIN_D, LEAVE_BEGIN_T, LEAVE_END_D, LEAVE_END_T, LEAVE_DAYS, LEAVE_REASON, BIAOSHI, HRBIAOSHI, XUHAO, INSERT_TIME, HR_UPDATE_TIME, LEAVE_TIMES, IS_FEISHU FROM ZZZ_HR_OA_QINGJIA WHERE LEAVE_END_D IS not null AND LEAVE_END_T IS not null AND TO_DATE(LEAVE_END_D || ' ' || LEAVE_END_T, 'YYYY-MM-DD HH24:MI') >= TO_DATE(?, 'YYYY-MM-DD HH24:MI') AND IS_FEISHU is null ### Cause: java.sql.SQLDataException: ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0 ; ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0 ; nested exception is java.sql.SQLDataException: ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0

 

解决办法

　　可以在SQL语句中加入如下条件，排除掉数据库中异常的数据。

  AND REGEXP_LIKE(LEAVE_END_D, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$') -- 确保日期格式正确
  AND REGEXP_LIKE(LEAVE_END_T, '^[0-9]{2}:[0-9]{2}$') -- 确保时间格式正确
  AND TO_NUMBER(SUBSTR(LEAVE_END_D, 1, 4)) BETWEEN 1 AND 9999 -- 确保年份有效