Bigquery Custom first day of week (defaults Monday)

Question

I am trying to extract week from date using the function

format_timestamp('%V', DATE)

but Bigquery documentation says "Monday as the first day of the week".

Is there a way where I can specify Tuesday as first day of week.

I have achieved the same in Redshift using

date_part(week, date + interval '-1 day')

Is it possible in Bigquery?

Answer 1

Below is for BigQuery Standard SQL

You can use WEEK(WEEKDAY) as shown below

#standardSQL
SELECT FORMAT_TIMESTAMP('%V', CURRENT_TIMESTAMP()), 
EXTRACT(WEEK(SATURDAY) FROM CURRENT_TIMESTAMP())

This was introduced quite recently - https://cloud.google.com/bigquery/docs/release-notes#november_30_2017