BigQuery中PARSE_DATE函数无法解析%Y%W格式日期的解决方案求助

我完全懂你的困扰——BigQuery的PARSE_DATE确实不支持%W这个格式元素，直接解析肯定卡壳。不过咱们可以手动拆解计算，完全能拿到你想要的对应周日日期。

解决方案思路 #

核心逻辑是先从week_year字符串里拆分出年份和周数，再定位该年份的第一个周一作为基准，最后推导对应周的周日：

1. 拆分年份与周数：从202201这类字符串里，提取前4位作为年份，后2位作为周数。
2. 定位年份第一个周一：因为%W的周是以周一为起始的，所以先找到目标年份的第一个周一，作为周数计算的起点。
3. 推导对应周的周日：从第一个周一出发，加上（周数-1）*7天到达目标周的周一，再加6天就是该周的周日。

完整SQL代码 #

SELECT
  week_year,
  -- 计算对应周的周日日期
  DATE_ADD(
    -- 先找到目标年份的第一个周一
    DATE_ADD(
      DATE(CAST(SUBSTRING(week_year, 1, 4) AS INT64), 1, 1),
      INTERVAL (
        CASE EXTRACT(DAYOFWEEK FROM DATE(CAST(SUBSTRING(week_year, 1, 4) AS INT64), 1, 1))
          WHEN 1 THEN 1    -- 1月1日是周日，加1天到周一
          WHEN 2 THEN 0    -- 1月1日是周一，无需调整
          ELSE 2 - EXTRACT(DAYOFWEEK FROM DATE(CAST(SUBSTRING(week_year, 1, 4) AS INT64), 1, 1)) + 7
        END
      ) DAY
    ),
    INTERVAL (CAST(SUBSTRING(week_year, 5, 2) AS INT64) - 1) * 7 + 6 DAY
  ) AS week_sunday
FROM
  -- 替换成你的实际表名
  your_table

测试验证 #

用你给出的示例数据测试，结果如下：

其中202201会正确返回2022-01-09，完全符合你的预期。

补充说明 #

• BigQuery的EXTRACT(DAYOFWEEK FROM date)返回规则：1=周日，2=周一，…，7=周六，这也是CASE语句判断逻辑的依据。
• 如果你的周定义是以周日为起始（从示例看你是周一起始），只需要调整CASE里的逻辑和后续天数偏移即可，核心思路完全通用。

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