如何在Big Query SQL中将DD-MMM-YY格式转换为YYYY/MM/DD格式

核心语法 #

你可以组合使用PARSE_DATE和FORMAT_DATE两个函数完成转换，对应格式符说明如下：

• %d：匹配两位天数
• %b：匹配英文缩写月份（如OCT、JAN，不区分大小写）
• %y：匹配两位年份，BigQuery默认规则为00-69对应2000-2069，70-99对应1970-1999

样例测试代码 #

-- 输入为'31-OCT-20'时，输出为2020-10-31的Date类型、以及'2020/10/31'的字符串类型
SELECT
  -- 仅解析为Date类型，适合目标表字段为Date类型的场景
  PARSE_DATE('%d-%b-%y', '31-OCT-20') AS parsed_date,
  -- 解析后格式化为YYYY/MM/DD字符串，适合目标表字段为STRING类型的场景
  FORMAT_DATE('%Y/%m/%d', PARSE_DATE('%d-%b-%y', '31-OCT-20')) AS formatted_date

实际表操作示例 #

目标表字段为Date类型 #

INSERT INTO `你的项目ID.你的数据集.目标表名` (日期字段名, 其他字段名)
SELECT
  PARSE_DATE('%d-%b-%y', 源表日期字符串字段名) AS 日期字段名,
  其他字段名
FROM `你的项目ID.你的数据集.源表名`

目标表字段为存储YYYY/MM/DD格式的STRING类型 #

INSERT INTO `你的项目ID.你的数据集.目标表名` (日期字符串字段名, 其他字段名)
SELECT
  FORMAT_DATE('%Y/%m/%d', PARSE_DATE('%d-%b-%y', 源表日期字符串字段名)) AS 日期字符串字段名,
  其他字段名
FROM `你的项目ID.你的数据集.源表名`

注意事项 #

• 源数据存在脏数据时，可以用SAFE.PARSE_DATE替换PARSE_DATE，遇到非法格式会返回NULL而非中断查询报错
• 两位年份的归属规则和默认逻辑不符时，可以手动加减年数调整，比如所有年份都归属2000年后可写为DATE_ADD(PARSE_DATE('%d-%b-%y', 源字段), INTERVAL 100 YEAR)适配业务规则

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