如何过滤varchar2类型无效日期值并转换插入至DATE类型字段

针对你提到的场景——源表varchar2类型的create_date字段存在无效值，需要将格式为MM/DD/YYYY的有效日期插入到目标表的DATE字段中，以下是几种实用方案：

方案1：使用VALIDATE_CONVERSION（Oracle 12cR2及以上版本） #

Oracle 12cR2新增的VALIDATE_CONVERSION函数可以直接验证字符串能否转换为指定数据类型，返回1表示有效，0表示无效，是最简洁的处理方式。

示例SQL：

INSERT INTO target_table (target_date_column)
SELECT TO_DATE(create_date, 'MM/DD/YYYY')
FROM source_table
WHERE VALIDATE_CONVERSION(create_date AS DATE, 'MM/DD/YYYY') = 1;

• 逻辑：先筛选出能按MM/DD/YYYY格式转换为DATE类型的记录，再将其转换后插入目标表。

方案2：旧版本兼容方案（Oracle 12cR2之前） #

如果你的数据库版本不支持VALIDATE_CONVERSION，可以通过以下两种方式处理：

2.1 正则表达式+异常规避 #

先通过正则过滤格式符合MM/DD/YYYY的字符串，再结合TO_DATE转换（注意：正则只能保证格式，无法验证日期合法性，比如02/30/2022这类无效日期需要额外处理）：

INSERT INTO target_table (target_date_column)
SELECT TO_DATE(create_date, 'MM/DD/YYYY')
FROM source_table
WHERE REGEXP_LIKE(create_date, '^[0-1][0-9]/[0-3][0-9]/[0-9]{4}$')
AND TO_DATE(create_date, 'MM/DD/YYYY') IS NOT NULL;

2.2 自定义函数判断有效性 #

创建一个自定义函数，通过捕获TO_DATE的转换异常来判断日期是否有效：

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

然后使用该函数筛选有效记录：

INSERT INTO target_table (target_date_column)
SELECT TO_DATE(create_date, 'MM/DD/YYYY')
FROM source_table
WHERE is_valid_date(create_date, 'MM/DD/YYYY') = 1;

注意事项 #

• 确保格式掩码'MM/DD/YYYY'与源数据的日期格式完全匹配，若源数据是其他格式（如DD/MM/YYYY）需调整掩码。
• 批量插入前建议先执行SELECT语句验证有效数据，避免插入失败：

  SELECT create_date, TO_DATE(create_date, 'MM/DD/YYYY') AS converted_date
  FROM source_table
  WHERE VALIDATE_CONVERSION(create_date AS DATE, 'MM/DD/YYYY') = 1;
  

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