MariaDB WEEK() Function

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

MariaDB WEEK() Syntax

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

WEEK(date)
WEEK(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.

Return value

The MariaDB WEEK() function returns the week of the given date in the current year, and the value range is from 0 to 53.

The following table organizes the processing logic of the WEEK() 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 WEEK() function will return NULL.

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

MariaDB WEEK() Examples

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

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

Output:

WEEK('2023-01-01', 0): 1
WEEK('2023-01-03', 0): 1
WEEK('2023-01-01', 1): 0
WEEK('2023-01-03', 1): 1
WEEK('2023-01-01', 2): 1
WEEK('2023-01-03', 2): 1
WEEK('2023-01-01', 3): 52
WEEK('2023-01-03', 3): 1
WEEK('2023-01-01', 4): 1
WEEK('2023-01-03', 4): 1
WEEK('2023-01-01', 5): 0
WEEK('2023-01-03', 5): 1
WEEK('2023-01-01', 6): 1
WEEK('2023-01-03', 6): 1
WEEK('2023-01-01', 7): 52
WEEK('2023-01-03', 7): 1

Conclusion

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