BigQuery查询持续返回‘Invalid date: '20.02.2017'’错误求助

运行以下BigQuery查询时，持续报错Invalid date: '20.02.2017'，尝试使用DATE_FORMAT后仍未解决，求排查关键处理步骤。

原查询语句：

SELECT
    EXTRACT(YEAR FROM CAST(Date AS Date)) AS Year,
    EXTRACT(MONTH FROM CAST(Date AS Date)) AS Month,
    ProductId,
    ROUND(MAX(UnitPrice),2) AS UnitPrice,
    SUM(Quantity) AS UnitsSold
FROM `mineral-anchor-426102-d8.sales.sales_info`
GROUP BY  
  Year,  
  Month,
  ProductId
ORDER BY  Year,  Month,  ProductId;

错误原因 #

1. BigQuery默认仅支持直接解析YYYY-MM-DD格式的字符串为日期类型，而你的数据中日期是DD.MM.YYYY格式（如20.02.2017），直接用CAST(Date AS Date)会触发格式不匹配错误。
2. DATE_FORMAT是将日期类型转为指定格式的字符串，不是用来解析字符串为日期的，所以用它无法解决转换问题。

修正方案 #

使用PARSE_DATE函数，指定日期格式%d.%m.%Y来解析DD.MM.YYYY格式的字符串：

SELECT
    EXTRACT(YEAR FROM PARSE_DATE('%d.%m.%Y', Date)) AS Year,
    EXTRACT(MONTH FROM PARSE_DATE('%d.%m.%Y', Date)) AS Month,
    ProductId,
    ROUND(MAX(UnitPrice), 2) AS UnitPrice,
    SUM(Quantity) AS UnitsSold
FROM `mineral-anchor-426102-d8.sales.sales_info`
GROUP BY  
    Year,  
    Month,
    ProductId
ORDER BY  
    Year,  
    Month,
    ProductId;

额外排查建议 #

如果数据中存在其他格式的异常日期，可使用SAFE.PARSE_DATE避免查询中断，同时筛选出无法解析的异常数据：

SELECT
    Date AS OriginalDate
FROM `mineral-anchor-426102-d8.sales.sales_info`
WHERE SAFE.PARSE_DATE('%d.%m.%Y', Date) IS NULL;

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