如何在Redshift中查找无法转换为date类型的异常字段值

Redshift虽不支持TRY_CAST，但自带专门的日期容错转换函数TRY_TO_DATE，转换失败时会返回NULL而非抛出全局报错，可直接用来定位异常值，操作步骤如下：

• 第一步：筛选出所有转换失败的异常子串
  执行以下查询直接拿到所有导致报错的非法日期字符串：

SELECT 
  <原字段名> AS 原始字段值,
  <你的日期子串提取逻辑> AS 提取到的子串
FROM <表名>
WHERE 
  TRY_TO_DATE(<你的日期子串提取逻辑>) IS NULL
  AND <你的日期子串提取逻辑> IS NOT NULL; -- 排除空值的合法场景

返回结果里的「提取到的子串」列所有值就是报错的根源，你可以直接查看这些值的格式问题，常见异常包括非法日期数值（如月份13、日期32）、特殊字符干扰、分隔符异常、年份位数不符等。

• 第二步：统计异常格式分布（可选）
  如果异常值数量较多，可先聚合统计高频异常类型，方便后续批量修复：

SELECT 
  <你的日期子串提取逻辑> AS 异常子串,
  COUNT(*) AS 出现次数
FROM <表名>
WHERE 
  TRY_TO_DATE(<你的日期子串提取逻辑>) IS NULL
  AND <你的日期子串提取逻辑> IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

提示：如果你提取的日期子串是固定非ISO默认格式，可在TRY_TO_DATE的第二个参数指定格式模板，比如TRY_TO_DATE(extracted_substring, 'DD/MM/YYYY')，避免合法的非默认格式被误判为异常。

• 第三步：兼容异常值实现无报错转换
  定位到异常格式后，可结合CASE语句自定义兼容逻辑，实现全量数据正常转换，示例如下：

SELECT 
  CASE
    WHEN TRY_TO_DATE(extracted_sub) IS NOT NULL THEN TRY_TO_DATE(extracted_sub)
    -- 兼容中文分隔符场景，例如2024年05月20日
    WHEN extracted_sub ~ '^\\d{4}年\\d{2}月\\d{2}日$' THEN TRY_TO_DATE(REGEXP_REPLACE(extracted_sub, '[年月日]', '-'))
    -- 可在这里新增你定位到的其他异常格式的处理逻辑
    ELSE NULL -- 也可替换为你需要的默认日期值
  END AS 转换后日期
FROM (
  SELECT <你的日期子串提取逻辑> AS extracted_sub FROM <表名>
) t

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