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:

2023-11-16, 333🔥, 0💬