执行SQL查询时遇ORA-01840日期格式错误，求解决方案

错误原因 #

报错ORA-01840: input value not long enough for date format的核心原因是attribute39字段中存在不符合MM/DD/YYYY格式的数据：

• 存在空字符串、长度不足的内容（比如仅MM/DD缺少年份）
• 存在格式不规范的字符串（比如分隔符错误、数字位数不够）

这些无效数据导致第一次TO_DATE(attribute39, 'MM/DD/YYYY')转换时，无法匹配指定的日期格式长度，触发报错。

解决方案 #

方案1：先过滤有效数据再转换 #

通过正则表达式筛选出符合MM/DD/YYYY格式的记录，只对有效数据执行转换：

SELECT TO_DATE (
          TO_CHAR (TO_DATE (attribute39, 'MM/DD/YYYY'), 'DD/MM/YYYY'),
          'DD/MM/YYYY') AS "PO Valid To Date"
  FROM table
 WHERE REGEXP_LIKE(attribute39, '^\d{2}/\d{2}/\d{4}$');

方案2：安全转换，无效数据返回NULL（Oracle 12c+） #

使用VALIDATE_CONVERSION函数判断数据是否可正常转换，无效数据返回NULL，避免整个查询报错：

SELECT CASE 
         WHEN VALIDATE_CONVERSION(attribute39 AS DATE, 'MM/DD/YYYY') = 1
         THEN TO_DATE (
                TO_CHAR (TO_DATE (attribute39, 'MM/DD/YYYY'), 'DD/MM/YYYY'),
                'DD/MM/YYYY')
         ELSE NULL
       END AS "PO Valid To Date"
  FROM table;

方案3：简化转换逻辑（可选） #

原SQL存在冗余转换：先把字符串转成日期，再转成DD/MM/YYYY字符串，又转回日期。其实日期类型本身没有格式，若最终需要的是日期类型，直接转换即可；若要显示为DD/MM/YYYY格式，只需在输出时用TO_CHAR：

-- 直接得到日期类型
SELECT TO_DATE(attribute39, 'MM/DD/YYYY') AS "PO Valid To Date"
FROM table
WHERE REGEXP_LIKE(attribute39, '^\d{2}/\d{2}/\d{4}$');

-- 若需要显示为DD/MM/YYYY格式的字符串
SELECT TO_CHAR(TO_DATE(attribute39, 'MM/DD/YYYY'), 'DD/MM/YYYY') AS "PO Valid To Date"
FROM table
WHERE REGEXP_LIKE(attribute39, '^\d{2}/\d{2}/\d{4}$');

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