如何解决Oracle SQL转换日期时出现的ORA-01841错误

你的问题出在column_name字段里存在格式不合法或年份超出范围（不在-4713至9999之间，或为0）的记录，导致to_timestamp_tz解析时抛出ORA-01841错误。以下是针对性的解决方法：

1. 先定位非法数据 #

先找出所有无法正常解析的记录，方便后续处理：

SELECT column_name
FROM TBL
WHERE NOT REGEXP_LIKE(column_name, '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}[+-]\d{2}:\d{2}$')
OR TO_NUMBER(SUBSTR(column_name, 1, 4)) NOT BETWEEN -4713 AND 9999
OR TO_NUMBER(SUBSTR(column_name, 1, 4)) = 0;

这条语句先用正则匹配标准格式，再单独校验年份合法性，精准定位问题行。

2. 转换时跳过非法数据 #

如果不想先清洗数据，直接在转换过程中忽略错误记录，分两种情况处理：

方法一：用VALIDATE_CONVERSION（Oracle 12c+） #

Oracle 12c及以上版本支持VALIDATE_CONVERSION函数，可提前校验字符串是否能转换为目标类型：

SELECT 
  CASE 
    WHEN VALIDATE_CONVERSION(column_name AS TIMESTAMP WITH TIME ZONE, 'YYYY-MM-DD"T"HH24:MI:SS.FF3 TZH:TZM') = 1
    THEN TO_CHAR(TO_TIMESTAMP_TZ(column_name, 'YYYY-MM-DD"T"HH24:MI:SS.FF3 TZH:TZM'), 'YYYYMMDDHH24MISS', 'NLS_CALENDAR=PERSIAN')
    ELSE '非法数据' -- 可替换为NULL或自定义标记
  END AS persian_datetime
FROM TBL;

返回1表示数据合法，会正常转换；返回0则标记为非法，避免报错。

方法二：自定义函数兼容低版本 #

如果你的Oracle版本低于12c，没有VALIDATE_CONVERSION，可以创建一个捕获异常的自定义函数：

CREATE OR REPLACE FUNCTION convert_to_persian_dt(p_str VARCHAR2) RETURN VARCHAR2 IS
  v_tz TIMESTAMP WITH TIME ZONE;
BEGIN
  v_tz := TO_TIMESTAMP_TZ(p_str, 'YYYY-MM-DD"T"HH24:MI:SS.FF3 TZH:TZM');
  RETURN TO_CHAR(v_tz, 'YYYYMMDDHH24MISS', 'NLS_CALENDAR=PERSIAN');
EXCEPTION
  WHEN OTHERS THEN
    RETURN '非法数据'; -- 也可以返回NULL
END;
/

调用函数完成转换：

SELECT convert_to_persian_dt(column_name) AS persian_datetime
FROM TBL;

3. 从根源清洗非法数据 #

如果需要彻底解决问题，直接修正或删除非法数据：

• 修正数据：对年份错误但可修正的记录，直接更新字段（示例：替换错误年份为2023）：

UPDATE TBL
SET column_name = REPLACE(column_name, SUBSTR(column_name, 1, 4), '2023')
WHERE TO_NUMBER(SUBSTR(column_name, 1, 4)) NOT BETWEEN -4713 AND 9999
OR TO_NUMBER(SUBSTR(column_name, 1, 4)) = 0;

• 删除数据：若非法数据无保留价值，直接删除：

DELETE FROM TBL
WHERE NOT REGEXP_LIKE(column_name, '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}[+-]\d{2}:\d{2}$')
OR TO_NUMBER(SUBSTR(column_name, 1, 4)) NOT BETWEEN -4713 AND 9999
OR TO_NUMBER(SUBSTR(column_name, 1, 4)) = 0;

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