如何在BigQuery中将任意日期/时间格式转换为dd/mm/yyyy并解决报错

云原生阿岚

2026-7-9

统一转换多种日期格式为dd/mm/yyyy的BigQuery更新语句

问题背景

需要将包含多种日期/时间格式的Date列统一转换为dd/mm/yyyy格式，原编写的更新语句执行时报错：

Mismatch between format character '/' and string character '-'

数据示例

2020/12/23
2020-03-24
20200524
07/30/2020
09-30-2021
09/20/20
12-24-20
20/09/2020
22-09-2023
09-2023
10-20
Jan2020
jan-2020
2020-jun
2020.12.22
12.22.2020
2020/04/22 12:30:09
2023-09-24 11:30:20
2022/09/23 11:20:30.22
2020-12-20 11:24:30.02
20220923141530
09/23/2022 14:15:30
09-23-2022 14:15:30

错误原语句

UPDATE `project.dataset.table`
SET Date =
  CASE 
    WHEN PARSE_TIMESTAMP('%Y/%m/%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d', Date))
    WHEN PARSE_TIMESTAMP('%Y-%m-%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d', Date))
    WHEN PARSE_DATE('%Y%m%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y%m%d', Date))
    WHEN PARSE_DATE('%m/%d/%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m/%d/%Y', Date))
    WHEN PARSE_DATE('%m-%d-%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%d-%Y', Date))
    WHEN PARSE_DATE('%m/%d/yy', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m/%d/yy', Date))
    WHEN PARSE_DATE('%m-%d-yy', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%d-yy', Date))
    WHEN PARSE_DATE('%d/%m/%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%d/%m/%Y', Date))
    WHEN PARSE_DATE('%d-%m-%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%d-%m-%Y', Date))
    WHEN PARSE_DATE('%m-%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-%Y', Date))
    WHEN PARSE_DATE('%m-yy', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m-yy', Date))
    WHEN PARSE_DATE('%b%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%b%Y', Date))
    WHEN PARSE_DATE('%b-%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%b-%Y', Date))
    WHEN PARSE_DATE('%Y-%b', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y-%b', Date))
    WHEN PARSE_DATE('%Y.%m.%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%Y.%m.%d', Date))
    WHEN PARSE_DATE('%m.%d.%Y', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_DATE('%m.%d.%Y', Date))
    WHEN PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S', Date))
    WHEN PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', Date))
    WHEN PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S.%f', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S.%f', Date))
    WHEN PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S.%f', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S.%f', Date))
    WHEN PARSE_TIMESTAMP('%Y%m%d%H%M%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%Y%m%d%H%M%S', Date))
    WHEN PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', Date))
    ELSE NULL
  END
WHERE TRUE;

报错原因

原语句中使用的PARSE_TIMESTAMP和PARSE_DATE函数在格式不匹配时会直接抛出错误，而非返回NULL，导致CASE逻辑无法跳过不匹配的格式，进而触发报错。

正确解决方案

使用BigQuery的SAFE.前缀函数（SAFE.PARSE_TIMESTAMP、SAFE.PARSE_DATE），这些函数在解析失败时返回NULL，不会中断执行。调整后的更新语句如下：

UPDATE `project.dataset.table`
SET Date =
  CASE 
    -- 处理带分隔符的日期格式
    WHEN SAFE.PARSE_TIMESTAMP('%Y/%m/%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%Y/%m/%d', Date))
    WHEN SAFE.PARSE_TIMESTAMP('%Y-%m-%d', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%Y-%m-%d', Date))
    WHEN SAFE.PARSE_DATE('%Y%m%d', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%Y%m%d', Date))
    WHEN SAFE.PARSE_DATE('%m/%d/%Y', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%m/%d/%Y', Date))
    WHEN SAFE.PARSE_DATE('%m-%d-%Y', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%m-%d-%Y', Date))
    WHEN SAFE.PARSE_DATE('%m/%d/yy', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%m/%d/yy', Date))
    WHEN SAFE.PARSE_DATE('%m-%d-yy', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%m-%d-yy', Date))
    WHEN SAFE.PARSE_DATE('%d/%m/%Y', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%d/%m/%Y', Date))
    WHEN SAFE.PARSE_DATE('%d-%m-%Y', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%d-%m-%Y', Date))
    WHEN SAFE.PARSE_DATE('%Y.%m.%d', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%Y.%m.%d', Date))
    WHEN SAFE.PARSE_DATE('%m.%d.%Y', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%m.%d.%Y', Date))
    -- 处理年月格式（默认当月第一天）
    WHEN SAFE.PARSE_DATE('%m-%Y', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%m-%Y', Date))
    WHEN SAFE.PARSE_DATE('%m-yy', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%m-yy', Date))
    WHEN SAFE.PARSE_DATE('%b%Y', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%b%Y', Date))
    WHEN SAFE.PARSE_DATE('%b-%Y', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%b-%Y', Date))
    WHEN SAFE.PARSE_DATE('%Y-%b', Date) IS NOT NULL THEN
      FORMAT_DATE('%d/%m/%Y', SAFE.PARSE_DATE('%Y-%b', Date))
    -- 处理带时间的格式
    WHEN SAFE.PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S', Date))
    WHEN SAFE.PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', Date))
    WHEN SAFE.PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S.%f', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%Y/%m/%d %H:%M:%S.%f', Date))
    WHEN SAFE.PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S.%f', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S.%f', Date))
    WHEN SAFE.PARSE_TIMESTAMP('%Y%m%d%H%M%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%Y%m%d%H%M%S', Date))
    WHEN SAFE.PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', Date))
    WHEN SAFE.PARSE_TIMESTAMP('%m-%d-%Y %H:%M:%S', Date) IS NOT NULL THEN
      FORMAT_TIMESTAMP('%d/%m/%Y', SAFE.PARSE_TIMESTAMP('%m-%d-%Y %H:%M:%S', Date))
    ELSE NULL
  END
WHERE TRUE;

说明

SAFE.前缀函数确保解析失败时返回NULL，CASE逻辑可以正常跳过不匹配的格式，避免报错。
对于仅包含年月的格式（如09-2023、Jan2020），解析后默认使用当月第一天作为日期，转换为01/09/2023、01/01/2020这类格式。
所有带时间戳的格式都会被提取日期部分并转换为目标格式。

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

如何在BigQuery中将任意日期/时间格式转换为dd/mm/yyyy并解决报错

问题背景 #

数据示例 #

错误原语句 #

报错原因 #

正确解决方案 #

说明 #

问题背景

数据示例

错误原语句

报错原因

正确解决方案

说明