WEEK()

USAGE

WEEK(date[, mode])

DETAILS

WEEK() is a date-time function that returns the week number for the given date.

If the mode argument is specified it controls three things:

• The starting day for the week (Sunday or a Monday)

• The week numbering idiom (0-53 or 1-53)

• The logic for how to determine the first week of the year

The returned week number can differ from the week number returned by YEARWEEK() due to how a year-spanning week is handled. Since the year number is not returned along with the week number, some days in the year can be considered to be a part of a week in the adjacent year. These are numbered either as a week 0 or a week beyond the last week of the year, depending on the requested mode.

The week number is based on the following supported modes:

Mode	First day of week	Week number range	Week 1	Early days
0	Sunday	0-53	Has a Sunday in this year	Week 0
1	Monday	0-53	Has 4 or more days in this year	Week 0
2	Sunday	1-53	Has a Sunday in this year	Week 52 or 53
3	Monday	1-53	Has 4 or more days in this year	Week 52 or 53
4	Sunday	0-53	Has 4 or more days in this year	Week 0
5	Monday	0-53	Has a Monday in this year	Week 0
6	Sunday	1-53	Has 4 or more days in this year	Week 52 or 53
7	Monday	1-53	Has a Monday in this year	Week 52 or 53

The default mode is taken from the system variable default_week_format when it is not specified.

EXAMPLES

SELECT WEEK('2020-02-01');

+--------------------+
| WEEK('2020-02-01') |
+--------------------+
|                  4 |
+--------------------+

SELECT WEEK('2020-01-02');

+--------------------+
| WEEK('2020-01-02') |
+--------------------+
|                  0 |
+--------------------+

CREATE TABLE day_list (
  d DATE
);

INSERT INTO day_list VALUES
  ('2028-01-01'), ('2028-01-02'), ('2028-01-03'),
  ('2028-12-30'), ('2028-12-31'), ('2029-01-01');

SELECT d AS 'date',
       DAYOFWEEK(d) AS DoW,
       WEEK(d,0) AS Sunday0,
       WEEK(d,2) AS Sunday2,
       WEEK(d,5) AS Monday5,
       WEEK(d,7) AS Monday7
FROM day_list;

+------------+------+---------+---------+---------+---------+
| date       | DoW  | Sunday0 | Sunday2 | Monday5 | Monday7 |
+------------+------+---------+---------+---------+---------+
| 2028-01-01 |    7 |       0 |      52 |       0 |      52 |
| 2028-01-02 |    1 |       1 |       1 |       0 |      52 |
| 2028-01-03 |    2 |       1 |       1 |       1 |       1 |
| 2028-12-30 |    7 |      52 |      52 |      52 |      52 |
| 2028-12-31 |    1 |      53 |      53 |      52 |      52 |
| 2029-01-01 |    2 |       0 |      53 |       1 |       1 |
+------------+------+---------+---------+---------+---------+

SELECT d AS 'date',
       DAYOFWEEK(d) AS DoW,
       WEEK(d,0),
       YEARWEEK(d,0)
FROM day_list;

+------------+------+-----------+---------------+
| date       | DoW  | WEEK(d,0) | YEARWEEK(d,0) |
+------------+------+-----------+---------------+
| 2028-01-01 |    7 |         0 |        202752 |
| 2028-01-02 |    1 |         1 |        202801 |
| 2028-01-03 |    2 |         1 |        202801 |
| 2028-12-30 |    7 |        52 |        202852 |
| 2028-12-31 |    1 |        53 |        202853 |
| 2029-01-01 |    2 |         0 |        202853 |
+------------+------+-----------+---------------+

ISO 9075:2016

The ISO 8601:1988 date and time standard is used in determining the week number.

CHANGE HISTORY