在BigQuery中从yyyy/mm/dd提取年份并转为mm-dd-yyyy格式的问题及报错解决

错误原因分析 #

你遇到的报错核心原因是：FORMAT_DATE函数要求第二个参数必须是DATE类型，但你的doj字段存储的是字符串（STRING），BigQuery找不到匹配的函数签名，所以抛出了类型不匹配的错误。

修正后的格式转换代码 #

要解决这个问题，需要先把字符串格式的日期解析成DATE类型，再进行格式化。这里用PARSE_DATE函数完成字符串到DATE的转换，再结合FORMAT_DATE输出你需要的mm-dd-yyyy格式：

with table1 as(
 select "2017/1/1" as doj union all
 select "2017/2/3" as doj union all
 select "2017/3/5" as doj union all
 select "2017/4/7" as doj union all
 select "2017/6/9" as doj
)
select format_date("%m-%d-%Y", PARSE_DATE("%Y/%m/%d", doj)) as doj 
from table1

年份提取的实现方法 #

如果要从字符串日期中提取年份，同样需要先解析成DATE类型，再用EXTRACT函数提取年份：

with table1 as(
 select "2017/1/1" as doj union all
 select "2017/2/3" as doj union all
 select "2017/3/5" as doj union all
 select "2017/4/7" as doj union all
 select "2017/6/9" as doj
)
select 
  doj as original_date,
  format_date("%m-%d-%Y", PARSE_DATE("%Y/%m/%d", doj)) as formatted_date,
  EXTRACT(YEAR FROM PARSE_DATE("%Y/%m/%d", doj)) as extracted_year
from table1

关键函数说明 #

• PARSE_DATE("%Y/%m/%d", doj)：按照yyyy/mm/dd的格式将字符串解析为DATE类型，格式符必须和输入字符串的结构完全对应（%Y对应4位年份，%m对应月份，%d对应日期）。
• FORMAT_DATE("%m-%d-%Y", date_value)：将DATE类型的日期按照指定格式输出为字符串。
• EXTRACT(YEAR FROM date_value)：从DATE类型中提取年份数值。

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