如何在SQL CASE语句中筛选提取STRING类型的指定格式日期

原语句问题梳理 #

• 条件判断逻辑错误：date = 'm/%d/%Y'是判断字段值等于格式字符串本身，无法实现日期格式校验的效果
• 日期解析格式不匹配：待解析的字符串为月/日/年格式，PARSE_DATE第一个参数需对应写%m/%d/%Y，原语句的%Y/%m/%d格式和输入不匹配会直接解析失败
• 字段名混淆：原语句中判断用的字段date和解析用的expiry_date不一致，需统一为你实际要处理的目标字段，以下方案默认以expiry_date为处理字段

最优实现方案 #

如果使用的是支持安全解析函数的SQL引擎（比如BigQuery），直接用SAFE.PARSE_DATE即可，该函数解析失败时会自动返回NULL，无需额外写判断逻辑：

SELECT
  SAFE.PARSE_DATE("%m/%d/%Y", expiry_date) AS formatted_date
FROM
  my_database

如果需要过滤掉所有无效值，只保留解析成功的日期行，补充WHERE条件即可：

SELECT
  SAFE.PARSE_DATE("%m/%d/%Y", expiry_date) AS formatted_date
FROM
  my_database
WHERE
  SAFE.PARSE_DATE("%m/%d/%Y", expiry_date) IS NOT NULL

通用兼容方案 #

如果所用SQL引擎不支持安全解析函数，可以先通过正则校验字符串格式，再做解析：

SELECT
  CASE WHEN 
    expiry_date REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$' 
    THEN PARSE_DATE("%m/%d/%Y", expiry_date)
    ELSE NULL
  END AS formatted_date
FROM
  my_database

不同数据库的正则匹配语法存在差异，例如PostgreSQL需替换REGEXP为~，可根据实际使用的数据库引擎做对应调整。

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