MariaDB YEARWEEK() Function

In MariaDB, YEARWEEK() is a built-in function that returns the year and week number of a given date.

MariaDB YEARWEEK() Syntax

This is the syntax of the MariaDB YEARWEEK() function:

YEARWEEK(date)
YEARWEEK(date, mode)

Parameters

date

Required. A date or datetime expression.

mode

Optional. Determine the logic for calculating weeks. If this parameter is not specified, the value of the default_week_format variable by default.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'YEARWEEK'.

Return value

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

The following table organizes the processing logic of the YEARWEEK() function on the mode parameter:

Mode First day of the week Return 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.

MariaDB YEARWEEK() Examples

Here are some common examples of the Mariadb YEARWEEK() function.

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

Output:

YEARWEEK('2023-01-01', 0): 202301
YEARWEEK('2023-01-03', 0): 202301
YEARWEEK('2023-01-01', 1): 202252
YEARWEEK('2023-01-03', 1): 202301
YEARWEEK('2023-01-01', 2): 202301
YEARWEEK('2023-01-03', 2): 202301
YEARWEEK('2023-01-01', 3): 202252
YEARWEEK('2023-01-03', 3): 202301
YEARWEEK('2023-01-01', 4): 202301
YEARWEEK('2023-01-03', 4): 202301
YEARWEEK('2023-01-01', 5): 202252
YEARWEEK('2023-01-03', 5): 202301
YEARWEEK('2023-01-01', 6): 202301
YEARWEEK('2023-01-03', 6): 202301
YEARWEEK('2023-01-01', 7): 202252
YEARWEEK('2023-01-03', 7): 202301

default_week_format

If no mode parameter, WEEK() uses the value of the default_week_format variable by default. You can view the value of the default_week_format variable as follows.

SHOW VARIABLES LIKE 'default_week_format';

Output:

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+

Conclusion

In MariaDB, YEARWEEK() is a built-in function that returns the year and week number of a given date.