Is there a way to use BigQuery's DAYOFWEEK but with Monday as its first day?

Question

I'm currently trying to map a date column into its day of the week with Monday being 1 and Sunday being 7 by using the following query:

EXTRACT(DAYOFWEEK FROM dates) AS day_of_week

However, according to BQ's documentation, it seems that the function uses Sunday as its first day of the week. Is there any way to elegantly solve this problem without the use of conditional expression in my query and manually adjust the result?

BQ Documentation:

DAYOFWEEK: Returns values in the range [1,7] with Sunday as the first day of the week.

Answer 1

You can use FORMAT_DATE() with the %u format specifier:

FORMAT_DATE('%u', dates)

The documentation describes %u as:

The weekday (Monday as the first day of the week) as a decimal number (1-7).

Answer 2

Perhaps the easiest approach here would be to just shift all your dates backwards by one days, such that a Monday would appear as a Sunday, e.g.

SELECT
    dates,
    EXTRACT(DAYOFWEEK FROM DATE_SUB(dates, INTERVAL 1 DAY)) AS day_of_week
FROM yourTable;

Keep in mind that this would mean that Sunday would be rolled back to become the 7th, or final, day of the week.