Oracle SQL中NUMBER(10)日期字段转日期类型报错求助

错误原因 #

ORA-01847错误的核心是你的CHEMO_DATE字段里存在无效的日期数字，比如像20160230（2月没有30号）、20161301（月份超过12）这类不符合日期规则的值，导致TO_DATE函数转换时失败。

解决步骤 #

1. 先找出所有无效的日期值 #

如果你的Oracle版本是12c及以上，直接用VALIDATE_CONVERSION函数筛选：

SELECT CHEMO_DATE
FROM 你的表名 TR
WHERE VALIDATE_CONVERSION(TR.CHEMO_DATE AS DATE, 'YYYYMMDD') = 0;

如果是12c以下版本，先创建一个检查函数：

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

然后查询无效值：

SELECT CHEMO_DATE
FROM 你的表名 TR
WHERE is_valid_date(CHEMO_DATE) = 0;

2. 转换时处理无效值 #

找到无效值后，要么修正这些错误数据，要么在转换时跳过或替换成NULL：

• 用VALIDATE_CONVERSION的写法（12c+）：

SELECT 
    CASE 
        WHEN VALIDATE_CONVERSION(TR.CHEMO_DATE AS DATE, 'YYYYMMDD') = 1 
        THEN TO_CHAR(TO_DATE(TR.CHEMO_DATE, 'YYYYMMDD'), 'mm/dd/yyyy') 
        ELSE NULL -- 这里可以换成你需要的默认值，比如'无效日期'
    END AS CHEM_DATE
FROM 你的表名 TR;

• 用自定义函数的写法（兼容所有版本）：

CREATE OR REPLACE FUNCTION convert_num_to_date_str(p_num NUMBER) RETURN VARCHAR2 IS
    v_date DATE;
BEGIN
    v_date := TO_DATE(p_num, 'YYYYMMDD');
    RETURN TO_CHAR(v_date, 'mm/dd/yyyy');
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
/

SELECT convert_num_to_date_str(TR.CHEMO_DATE) AS CHEM_DATE
FROM 你的表名 TR;

3. 额外提示 #

如果你的需求是将字段转为日期类型（不是字符串格式的日期），直接用TO_DATE即可，不需要嵌套TO_CHAR：

SELECT 
    CASE 
        WHEN VALIDATE_CONVERSION(TR.CHEMO_DATE AS DATE, 'YYYYMMDD') = 1 
        THEN TO_DATE(TR.CHEMO_DATE, 'YYYYMMDD') 
        ELSE NULL 
    END AS CHEM_DATE
FROM 你的表名 TR;

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