YEARWEEK() - Year Week Combination

Q

How to calculate the year week combination from a given date using the YEARWEEK() function?

✍: FYIcenter.com

A

YEARWEEK(date, mode) is a MySQL built-in function that calculates the year week combination from a given date. For example:

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

SELECT YEARWEEK('1987-01-01'), WEEK('1987-01-01');
  -- +------------------------+--------------------+
  -- | YEARWEEK('1987-01-01') | WEEK('1987-01-01') |
  -- +------------------------+--------------------+
  -- |                 198652 |                  0 |
  -- +------------------------+--------------------+

SELECT YEARWEEK('1987-01-01', 0), YEARWEEK('1987-01-01', 1);
  -- +---------------------------+---------------------------+
  -- | YEARWEEK('1987-01-01', 0) | YEARWEEK('1987-01-01', 1) |
  -- +---------------------------+---------------------------+
  -- |                    198652 |                    198701 |
  -- +---------------------------+---------------------------+

Reference information of the YEARWEEK() function:

YEARWEEK(date, mode): int
  Returns year and week for a date. The year in the result may be
  different from the year in the date argument for the first and the
  last week of the year.

  The mode argument works exactly like the mode argument to WEEK().

Arguments, return value and availability:
  date: Required. The date to extract the year week combination from.
  mode: Optional. Default is 0. The week mode to control how the first week
    is defined.
  int: Return value. The year week combination.
  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:

 

MySQL Functions on Aggregation Groups

YEAR() - Year of Date

MySQL Functions on Date and Time

⇑⇑ MySQL Function References

2023-11-16, 333🔥, 0💬