How to calculate the week of year from a given date using the WEEK() function?

WEEK(date, mode) is a MySQL built-in function that returns the week of year from a given date. For example:

SELECT WEEK('2023-02-03'), WEEK(NOW()), NOW();
  -- +--------------------+-------------+---------------------+
  -- | WEEK('2023-02-03') | WEEK(NOW()) | NOW()               |
  -- +--------------------+-------------+---------------------+
  -- |                  5 |          46 | 2023-11-16 07:20:11 |
  -- +--------------------+-------------+---------------------+

SELECT WEEK('2024-03-04'), WEEK('2024-03-04', 0);
  -- +--------------------+-----------------------+
  -- | WEEK('2024-03-04') | WEEK('2024-03-04', 0) |
  -- +--------------------+-----------------------+
  -- |                  9 |                     9 |
  -- +--------------------+-----------------------+

SELECT WEEK('2024-03-04', 0), WEEK('2024-03-04', 1), DAYOFWEEK('2024-01-01');
  -- +-----------------------+-----------------------+-------------------------+
  -- | WEEK('2024-03-04', 0) | WEEK('2024-03-04', 1) | DAYOFWEEK('2024-01-01') |
  -- +-----------------------+-----------------------+-------------------------+
  -- |                     9 |                    10 |                       2 |
  -- +-----------------------+-----------------------+-------------------------+

Reference information of the WEEK() function:

WEEK(date, mode): int
  Returns the week number for date. The two-argument form of WEEK()
  enables you to specify whether the week starts on Sunday or Monday and
  whether the return value should be in the range from 0 to 53 or from 1
  to 53. If the mode argument is omitted, the value of the
  default_week_format system variable is used.

Arguments, return value and availability:
  date: Required. The date to extract the week of year from.
  mode: Optional. Default is 0. The week mode to control how the first week
    is defined.
  int: Return value. The week of year.
  Available since MySQL 4.

Week modes:
  Mode  1st day of week  Range  Week 1 is the first week
  ----  ---------------  -----  ------------------------
  0     Sunday           0-53   with a Sunday in this year
  1     Monday           0-53   with 4 or more days this year
  2     Sunday           1-53   with a Sunday in this year
  3     Monday           1-53   with 4 or more days this year
  4     Sunday           0-53   with 4 or more days this year
  5     Monday           0-53   with a Monday in this year
  6     Sunday           1-53   with 4 or more days this year
  7     Monday           1-53   with a Monday in this year

Related MySQL functions:

• WEEKOFYEAR() - Calendar Week of Year
• DAYOFWEEK() - Day of Week (1=Sunday)
• DAYOFMONTH() - Day of Month
• DAYOFYEAR() - Day of Year

⇒ WEEKDAY() - Weekday (0=Monday)

⇐ UTC_TIMESTAMP() - Current UTC Timestamp

⇑ MySQL Functions on Date and Time

⇑⇑ MySQL Function References