MySQL WEEK() Function

In MySQL, the WEEK() function returns the week number in the current year for the gaven date.

This function is similar to YEARWEEK().

WEEK() Syntax

Here is the syntax of MySQL WEEK() function:

WEEK(date)
WEEK(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 WEEK() function returns the week number of the current year for the given date, in the range from 0 to 53.

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 date is not a valid date or datetime, the WEEK() function will return NULL.
  • If the argument is NULL, the WEEK() function will return NULL.

WEEK() Examples

Here are some examples of the WEEK() function.

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

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