MySQL YEARWEEK() Function

In MySQL, the YEARWEEK() function returns a number representing the year and week of the given date. This function is similar to WEEK().

YEARWEEK() Syntax

Here is the syntax of MySQL YEARWEEK() function:

YEARWEEK(date)
YEARWEEK(date, mode)

Parameters

date
Required. A date or datetime expression.
mode
Optional. It indicates the logic for calculating weeks. If this parameter is not specified, the variable default_week_format will be used by default.

Return value

The MySQL YEARWEEK() function returns a number representing the year and week of the given date.

The following table lists all modes the WEEK() function:

Mode first day of the week return value range Requirements for the first week
0 Sunday 0-53
1 Monday 0-53 At least 4 days in the current year
2 Sunday 1-53
3 Monday 1-53 At least 4 days in the current year
4 Sunday 0-53 At least 4 days in the current year
5 Monday 0-53
6 Sunday 1-53 At least 4 days in the current year
7 Monday 1-53

If the specified expression is not a valid date or datetime, the YEARWEEK() function will return NULL.

If the argument is NULL, the YEARWEEK() function will return NULL.

YEARWEEK() Examples

Here are some examples of the YEARWEEK() function.

SELECT
    YEARWEEK('2022-01-01', 0),
    YEARWEEK('2022-01-03', 0),
    YEARWEEK('2022-01-01', 1),
    YEARWEEK('2022-01-03', 1),
    YEARWEEK('2022-01-01', 2),
    YEARWEEK('2022-01-03', 2),
    YEARWEEK('2022-01-01', 3),
    YEARWEEK('2022-01-03', 3),
    YEARWEEK('2022-01-01', 4),
    YEARWEEK('2022-01-03', 4),
    YEARWEEK('2022-01-01', 5),
    YEARWEEK('2022-01-03', 5),
    YEARWEEK('2022-01-01', 6),
    YEARWEEK('2022-01-03', 6),
    YEARWEEK('2022-01-01', 7),
    YEARWEEK('2022-01-03', 7)\G

YEARWEEK('2022-01-01', 0): 202152
YEARWEEK('2022-01-03', 0): 202201
YEARWEEK('2022-01-01', 1): 202152
YEARWEEK('2022-01-03', 1): 202201
YEARWEEK('2022-01-01', 2): 202152
YEARWEEK('2022-01-03', 2): 202201
YEARWEEK('2022-01-01', 3): 202152
YEARWEEK('2022-01-03', 3): 202201
YEARWEEK('2022-01-01', 4): 202152
YEARWEEK('2022-01-03', 4): 202201
YEARWEEK('2022-01-01', 5): 202152
YEARWEEK('2022-01-03', 5): 202201
YEARWEEK('2022-01-01', 6): 202152
YEARWEEK('2022-01-03', 6): 202201
YEARWEEK('2022-01-01', 7): 202152
YEARWEEK('2022-01-03', 7): 202201

default_week_format

If mode parameter is not specified, WEEK() use the default_week_format variable by default. We can view the value of the default_week_format variable.

SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+