BigQuery中DATETIME字符串转换为指定日期、年月格式的实现方法

问题原因 #

你现有代码的错误出在第二个PARSE_DATE逻辑：原日期字符串前10位才是完整的dd/mm/YYYY格式内容，取前7位得到的是不完整的日期片段，无法匹配%d/%m/%Y的解析规则，会直接报错。

正确写法 #

你不需要重复解析字符串，先把完整的日期部分解析为DATE类型，再基于该值格式化出你需要的两种输出即可。

如果你用的是BigQuery（匹配你用的PARSE_DATE语法）：

-- OUTPUT1：DATE类型的2019-12-17，需要字符串格式的话可外层套FORMAT_DATE('%Y-%m-%d', ...)
PARSE_DATE('%d/%m/%Y', LEFT(COMPLETED_DATE, 10)) AS COMPLETED_DATE,
-- OUTPUT2：字符串类型的YYYYMM格式
FORMAT_DATE('%Y%m', PARSE_DATE('%d/%m/%Y', LEFT(COMPLETED_DATE, 10))) AS CALENDAR_MONTH

如果要避免字符串截取的潜在误差，也可以直接解析完整的带时间的字符串：

-- 先解析完整的12小时制DATETIME，再按需转换
DATE(PARSE_DATETIME('%d/%m/%Y %r', COMPLETED_DATE)) AS COMPLETED_DATE,
FORMAT_DATETIME('%Y%m', PARSE_DATETIME('%d/%m/%Y %r', COMPLETED_DATE)) AS CALENDAR_MONTH

如果你用的是MySQL：

STR_TO_DATE(LEFT(COMPLETED_DATE, 10), '%d/%m/%Y') AS COMPLETED_DATE,
DATE_FORMAT(STR_TO_DATE(LEFT(COMPLETED_DATE, 10), '%d/%m/%Y'), '%Y%m') AS CALENDAR_MONTH

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