YEARWEEK()

USAGE

YEARWEEK(date[, mode])

DETAILS

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

The returned numeric value is in the format YYYYWW where the week number is added to the year*100.

If the mode argument is specified, it controls two things:

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

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

The returned year can be different than the year in the input date for a week that spans 2 years.

Unlike WEEK(), the returned week number will never be 0 because such a day is returned as the prior year's last week number. This means that modes numbers that only differ in their week number range in the WEEK() function's list generate the same return values here.

The returned week number is based on the following supported modes:

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

The default mode is 0 when it is not specified (the system variable default_week_format is not consulted).

EXAMPLES

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

+------------------------+
| YEARWEEK('2020-02-01') |
+------------------------+
|                 202004 |
+------------------------+

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

+------------------------+
| YEARWEEK('2020-01-02') |
+------------------------+
|                 201952 |
+------------------------+

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,
       YEARWEEK(d,0) AS Sunday0,
       YEARWEEK(d,2) AS Sunday2,
       YEARWEEK(d,5) AS Monday5,
       YEARWEEK(d,7) AS Monday7
FROM day_list;

+------------+------+---------+---------+---------+---------+
| date       | DoW  | Sunday0 | Sunday2 | Monday5 | Monday7 |
+------------+------+---------+---------+---------+---------+
| 2028-01-01 |    7 |  202752 |  202752 |  202752 |  202752 |
| 2028-01-02 |    1 |  202801 |  202801 |  202752 |  202752 |
| 2028-01-03 |    2 |  202801 |  202801 |  202801 |  202801 |
| 2028-12-30 |    7 |  202852 |  202852 |  202852 |  202852 |
| 2028-12-31 |    1 |  202853 |  202853 |  202852 |  202852 |
| 2029-01-01 |    2 |  202853 |  202853 |  202901 |  202901 |
+------------+------+---------+---------+---------+---------+

ISO 9075:2016

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

CHANGE HISTORY