将数据库文本字段转换为日期时出错，寻求解决方案

核心问题 #

你遇到的错误是因为JobCompletedDate字段中存在不符合yyyy-MM-dd格式的文本值，或是混淆了PARSE_DATE函数的格式符大小写规则——BigQuery中MM代表两位数月份，mm代表分钟，错误提示里的yyyy-mm-dd说明解析时遇到了与指定格式不匹配的内容。

修复步骤 #

1. 排查异常数据
   先找出所有无法用目标格式解析的文本值，用SAFE.PARSE_DATE避免报错：

   SELECT JobCompletedDate
   FROM your_table
   WHERE JobCompletedDate IS NOT NULL
     AND SAFE.PARSE_DATE("yyyy-MM-dd", JobCompletedDate) IS NULL
   

   执行后就能看到哪些行的日期格式不符合要求（比如带时间、用斜杠分隔、单数字月份/日期等）。

2. 调整解析公式

   • 如果字段日期确实是2024-04-25格式，直接用SAFE.PARSE_DATE简化处理，它会自动返回空值给原空行和格式错误的行：

     SELECT SAFE.PARSE_DATE("yyyy-MM-dd", JobCompletedDate) AS CompletedDate
     FROM your_table
     

   • 若需要保留原CASE WHEN逻辑，确保格式符是yyyy-MM-dd（注意MM为大写）：

     SELECT 
       CASE WHEN JobCompletedDate IS NULL THEN NULL ELSE PARSE_DATE("yyyy-MM-dd", JobCompletedDate) END AS CompletedDate
     FROM your_table
     

3. 处理多格式场景
   如果字段里存在多种日期格式（比如2024-04-25和2024/04/25），用COALESCE依次尝试解析：

   SELECT 
     COALESCE(
       SAFE.PARSE_DATE("yyyy-MM-dd", JobCompletedDate),
       SAFE.PARSE_DATE("yyyy/MM/dd", JobCompletedDate),
       SAFE.PARSE_DATE("yyyy-M-d", JobCompletedDate)
     ) AS CompletedDate
   FROM your_table
   

关键提醒 #

BigQuery的PARSE_DATE格式符严格区分大小写：

• yyyy=4位年份，MM=两位数月份，dd=两位数日期
• mm=分钟，DD=年中天数，别搞混导致解析失败

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