BigQuery StandardSQL returns 2022-52 as year-isoweek for 2022-01-01-CodePudding

In BigQuery StandardSQL, format_date("%Y-%V",DATE "2022-01-01") returns 2022-52 while it should actually be 2021-52. Does anyone know how to fix this/ how to get the correct year for the respective ISO-WEEK?

CodePudding user response：

I think this is what you're after:

select EXTRACT(ISOYEAR FROM DATE "2022-01-01") as theisoyear,
       EXTRACT(ISOWEEK FROM DATE "2022-01-01") AS theisoweek;

CodePudding user response：

With %G for ISO year instead of %Y,

select format_date("%G-%V",DATE "2022-01-01")

you can get 2021-51.