Extract/Trunc WEEK starting on Mon (not Sun)

Extract/Trunc WEEK starting on Mon (not Sun)

Description

The EXTRACT(WEEK from <date>) functionality is very using in BigQuery for quickly grouping actions into dates, but the default week begins on Sunday.
For us, it makes more sense for our weeks to begin on Mondays. To do that you can do:

EXTRACT(WEEK(<WEEKDAY>) FROM <DATE>) as Week

or for DATE_TRUNC(<DATE>,WEEK), you can do:

DATE_TRUNC(<DATE>,WEEK(<WEEKDAY>) as week_commencing

where:

  • <WEEKDAY> is MONDAY, TUESDAY, …, SUNDAY.
  • <DATE> is the date column you’re transforming

Example