BigQuery解析仅含YY格式的日期：强制指定1900年代

一、关于%C格式符的正确用法 #

BigQuery的parse_date里，%C是用来匹配输入字符串中已存在的两位世纪数字的（比如19或20），但你的输入是dd-mmm-yy（没有世纪部分），所以直接在格式串里加%C完全无效——因为原字符串没有对应位置的字符可以匹配。

二、两种可行的解决方法 #

方法1：强制指定为19世纪（适用于所有出生日期都是19xx的场景） #

直接拼接年份前的19，用四位年份格式%Y解析：

PARSE_DATE("%d-%h-%Y", CONCAT(LEFT(DATE_OF_BIRTH, 6), "19", RIGHT(DATE_OF_BIRTH, 2))) AS CORRECT_DOB

比如原字符串25-Jul-47会被拼接成25-Jul-1947，解析后就是正确的1947年日期。

方法2：结合age字段用CASE WHEN动态调整（兼顾19xx和20xx的场景） #

如果存在少量2000年后出生的记录，用年龄合理性判断更准确：

CASE
  -- 假设人类合理寿命不超过120岁，解析后年龄超过则减100年
  WHEN DATE_DIFF(CURRENT_DATE(), PARSE_DATE("%d-%h-%y", DATE_OF_BIRTH), YEAR) > 120
  THEN PARSE_DATE("%d-%h-%y", DATE_OF_BIRTH) - INTERVAL 100 YEAR
  ELSE PARSE_DATE("%d-%h-%y", DATE_OF_BIRTH)
END AS CORRECT_DOB

或者直接匹配age字段的数值：

CASE
  -- 若解析出的日期计算出的年龄和age字段不符，说明世纪错了
  WHEN DATE_DIFF(CURRENT_DATE(), PARSE_DATE("%d-%h-%y", DATE_OF_BIRTH), YEAR) != age
  THEN PARSE_DATE("%d-%h-%y", DATE_OF_BIRTH) - INTERVAL 100 YEAR
  ELSE PARSE_DATE("%d-%h-%y", DATE_OF_BIRTH)
END AS CORRECT_DOB

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