Google Bigquery Legacy SQL - How to set week number start to Monday?

Question

I've heard the UTC_USEC_TO_WEEK() function in legacy sql allows you to set the start week to monday (and not sunday which is the default). Basically, I want the below date queried to return 1 and not 2.

select week('2018-01-07') -- returns 2

I've seen how to do this in standard sql but I'm querying a legacy sql view so I can't switch to standard sql.

How do I go about using UTC_USEC_TO_WEEK to get monday as the week start?

Documentation isn't much help and can't find anything online. Any help with this would be much appreciated!

Answer 1

Just move one day back with DATE_ADD() and then apply WEEK()

#legacySQL
SELECT WEEK(DATE_ADD('2018-01-07', -1, "DAY"))

Answer 2

Essentially was looking for this week(utc_usec_to_week(current_date(),1)) - 1, so this would return the previous week with Monday as the start of the week.