week_of_year starting on 'sunday'

[rafaella_gomes]

Hi guys,

Does anyone know how to get weeks starting on Sundays? I need to extract the week_of_year, but it is currently based on the ISO standard, which uses Monday as the start of the weeks. I would like to adjust it to start on Sundays instead. Any help would be appreciated!

[prabhakaran_mai]

@rafaella_gomesUnfortunately week_of_year follows the ISO standards and that cannot be changed. As per the documentation week_start_day parameter does not have any impact on week_of_year.

So you have to calculate it using SQL, below SQL expression works in BigQuery

EXTRACT(WEEK FROM DATE_SUB(date, INTERVAL (8 - EXTRACT(DAYOFWEEK FROM date)) DAY)) as week_number