如何在BigQuery中将任意日期/时间格式转换为dd-mm-yyyy？

我有一个包含多种日期格式和日期时间格式的Date列，希望将其统一转换为dd-mm-yyyy格式。使用TIMESTAMP_DIFF、PARSE_TIMESTAMP、FORMAT_TIMESTAMP和FORMAT_DATE函数后，多数格式能正常转换，但遇到dd/mm/yy HH:MM这类带时间的日期格式时会抛出错误。请问如何编写BigQuery的更新语句，实现任意日期/时间格式的转换？

示例数据 #

data:-
0           21/12/2006
1           25/01/2007
2           20/02/2023
3           1996.12.25
4           24/12/2020
5           24/12/2020
6     24/05/2020 12:02
7     24/05/2020 12:02
8     24/05/2020 12:02
9     24/05/2020 12:02
10          20/02/2023
11          21/02/2023
12          22/02/2023
13          23/02/2023
14          24/02/2023
15          25/02/2023
16          26/02/2023
17          02/12/2020
18          02/23/2020
19          09/23/2020
Name: Date, dtype: object

当前使用的BigQuery语句 #

UPDATE table_name
SET
   Date = CASE
    WHEN TIMESTAMP_DIFF(PARSE_TIMESTAMP('%d/%m/%Y', Date), TIMESTAMP_TRUNC(PARSE_TIMESTAMP('%d/%m/%Y', Date), DAY), SECOND) > 0 THEN
      FORMAT_TIMESTAMP('%d%m%y', PARSE_TIMESTAMP('%d/%m/%Y', Date))
    ELSE
      FORMAT_DATE('%d%m%y', PARSE_DATE('%d/%m/%Y', Date))
  END
WHERE TRUE;

报错信息 #

Error:
Failed to parse input string "24/05/2020 12:02"

要处理多种日期/时间格式，核心是利用BigQuery的SAFE.PARSE_TIMESTAMP和SAFE.PARSE_DATE函数（解析失败时返回NULL，不会触发报错），按顺序尝试匹配不同的格式，最后统一转换为dd-mm-yyyy格式。

以下是适配你所有示例格式的更新语句：

UPDATE table_name
SET Date = FORMAT_DATE('%d-%m-%Y', 
  COALESCE(
    -- 先尝试解析带时间的格式：dd/mm/yyyy HH:MM
    DATE(SAFE.PARSE_TIMESTAMP('%d/%m/%Y %H:%M', Date)),
    -- 尝试解析dd/mm/yyyy格式
    SAFE.PARSE_DATE('%d/%m/%Y', Date),
    -- 尝试解析yyyy.mm.dd格式
    SAFE.PARSE_DATE('%Y.%m.%d', Date),
    -- 尝试解析mm/dd/yyyy格式（针对示例中02/23/2020这类格式）
    SAFE.PARSE_DATE('%m/%d/%Y', Date)
  )
)
-- 只更新能成功解析的行，避免将无法解析的值设为NULL
WHERE COALESCE(
    DATE(SAFE.PARSE_TIMESTAMP('%d/%m/%Y %H:%M', Date)),
    SAFE.PARSE_DATE('%d/%m/%Y', Date),
    SAFE.PARSE_DATE('%Y.%m.%d', Date),
    SAFE.PARSE_DATE('%m/%d/%Y', Date)
) IS NOT NULL;

语句说明： #

1. SAFE.PARSE_TIMESTAMP/SAFE.PARSE_DATE：解析失败时返回NULL，不会中断整个更新操作，确保其他格式的行能正常处理。
2. COALESCE：按顺序检查每个解析结果，返回第一个非NULL的值，优先匹配带时间的格式，再依次匹配其他纯日期格式。
3. DATE()：将解析出的时间戳转换为日期类型，忽略时间部分，统一只保留日期信息。
4. FORMAT_DATE('%d-%m-%Y', ...)：将最终的日期类型格式化为dd-mm-yyyy字符串。
5. WHERE条件：只更新能成功解析的行，避免把原本无法识别的日期值设置为NULL，保留原始数据以便后续处理。

如果还有其他特殊格式，可以继续在COALESCE中添加对应的SAFE.PARSE_DATE或SAFE.PARSE_TIMESTAMP语句，按匹配优先级排序即可。

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