MySQL | YEARWEEK method
Start your free 7-days trial now!
MySQL's YEARWEEK(~) method returns the year and week for a date or datetime.
The year in the returned value may differ from the year in the input date for first and last weeks of the year.
For example, if 1 Jan 2020 does not meet the definition of first week for 2020 as defined by the specified mode, it will be considered the last week of 2019.
Parameters
1. date | date/datetime
The date/datetime to return the year and week number for.
2. mode | number | optional
The convention for how a week should be defined. Defaults to Mode 0.
Mode | First day of week | Range | Week 1 is the first week ... |
|---|---|---|---|
0 | Sunday | 0-53 | with a Sunday |
1 | Monday | 0-53 | with 4 or more days |
2 | Sunday | 1-53 | with a Sunday |
3 | Monday | 1-53 | with 4 or more days |
4 | Sunday | 0-53 | with 4 or more days |
5 | Monday | 0-53 | with a Monday |
6 | Sunday | 1-53 | with 4 or more days |
7 | Monday | 1-53 | with a Monday |
Return value
The year and week for the input date or datetime.
Examples
Basic usage
To return the year and week for 2020-01-01 as per Mode 0 definition:
SELECT YEARWEEK('2020-01-01');
+------------------------+| YEARWEEK('2020-01-01') |+------------------------+| 201952 |+------------------------+
The first Sunday in 2020 is on 5th Jan. Therefore 1st - 4th Jan 2020 is still considered part of 52nd week of 2019.
Mode parameter
To return the year and week for 2020-01-01 as per Mode 1 definition:
SELECT YEARWEEK('2020-01-01', 1);
+---------------------------+| YEARWEEK('2020-01-01', 1) |+---------------------------+| 202001 |+---------------------------+
As the week containing 1 Jan 2020 has 4 or more days, it is considered Week 1 of 2020.
