如何在Google BigQuery (SQL)中将复杂字符串日期转为DATETIME

直接用嵌套CASE结合SAFE解析函数，就能覆盖你提到的所有格式，同时把无效值转为NULL，无时间的自动补00:00:00：

SELECT
  data_pt_filtro AS original_date_str,
  CASE
    -- 处理巴西带时间格式（兼容有无前导零）
    WHEN SAFE.PARSE_DATETIME('%e/%c/%Y - %k:%M', data_pt_filtro) IS NOT NULL 
      THEN SAFE.PARSE_DATETIME('%e/%c/%Y - %k:%M', data_pt_filtro)
    WHEN SAFE.PARSE_DATETIME('%d/%m/%Y - %H:%M', data_pt_filtro) IS NOT NULL 
      THEN SAFE.PARSE_DATETIME('%d/%m/%Y - %H:%M', data_pt_filtro)
    -- 处理巴西不带时间格式（兼容有无前导零），转DATETIME自动补00:00:00
    WHEN SAFE.PARSE_DATE('%e/%c/%Y', data_pt_filtro) IS NOT NULL 
      THEN DATETIME(SAFE.PARSE_DATE('%e/%c/%Y', data_pt_filtro))
    WHEN SAFE.PARSE_DATE('%d/%m/%Y', data_pt_filtro) IS NOT NULL 
      THEN DATETIME(SAFE.PARSE_DATE('%d/%m/%Y', data_pt_filtro))
    -- 处理美式yyyy-mm-dd格式
    WHEN SAFE.PARSE_DATE('%Y-%m-%d', data_pt_filtro) IS NOT NULL 
      THEN DATETIME(SAFE.PARSE_DATE('%Y-%m-%d', data_pt_filtro))
    -- 所有无效值返回NULL
    ELSE NULL
  END AS cleaned_datetime
FROM Sales;

关键细节说明 #

• SAFE前缀函数：SAFE.PARSE_DATETIME和SAFE.PARSE_DATE在解析失败时返回NULL，不会中断整个查询，完美适配脏数据场景。
• 格式符对应：
  • %e/%c/%k：匹配无前导零的日、月、小时
  • %d/%m/%H：匹配有前导零的日、月、小时
  • %M：匹配分钟（无论有无前导零都能解析）
• 无时间补全：先转成DATE类型，再用DATETIME()函数转换，会自动补全00:00:00的时间部分。

额外优化：清理明确的无效值 #

如果你的数据里有大量(error)这类明确无效的字符串，可以先提前清理，减少后续解析判断：

SELECT
  data_pt_filtro AS original_date_str,
  CASE
    WHEN cleaned_str IS NULL THEN NULL
    ELSE CASE
           WHEN SAFE.PARSE_DATETIME('%e/%c/%Y - %k:%M', cleaned_str) IS NOT NULL 
             THEN SAFE.PARSE_DATETIME('%e/%c/%Y - %k:%M', cleaned_str)
           WHEN SAFE.PARSE_DATETIME('%d/%m/%Y - %H:%M', cleaned_str) IS NOT NULL 
             THEN SAFE.PARSE_DATETIME('%d/%m/%Y - %H:%M', cleaned_str)
           WHEN SAFE.PARSE_DATE('%e/%c/%Y', cleaned_str) IS NOT NULL 
             THEN DATETIME(SAFE.PARSE_DATE('%e/%c/%Y', cleaned_str))
           WHEN SAFE.PARSE_DATE('%d/%m/%Y', cleaned_str) IS NOT NULL 
             THEN DATETIME(SAFE.PARSE_DATE('%d/%m/%Y', cleaned_str))
           WHEN SAFE.PARSE_DATE('%Y-%m-%d', cleaned_str) IS NOT NULL 
             THEN DATETIME(SAFE.PARSE_DATE('%Y-%m-%d', cleaned_str))
           ELSE NULL
         END
  END AS cleaned_datetime
FROM (
  SELECT
    data_pt_filtro,
    -- 将(error)和空字符串转为NULL
    CASE 
      WHEN data_pt_filtro IN ('(error)', '') THEN NULL 
      ELSE data_pt_filtro 
    END AS cleaned_str
  FROM Sales
);

测试示例 #

内容的提问来源于stack exchange，提问作者João Pedro Reis Silva