MySQL | WEEK method
Start your free 7-days trial now!
MySQL's WEEK(~) method returns the week number from a given date or datetime.
Parameters
1. date | date/datetime
The date/datetime to retrieve the week number from.
2. mode | number | optional
The convention for how a week should be defined. Defaults to value of default_week_format variable.
You can check for the value of the default by running the below:
SHOW VARIABLES LIKE 'default_week_format';
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 week number from the input date or datetime.
Examples
Basic usage
To return week number for April 26, 2020:
SELECT WEEK('2020-04-26');
+--------------------+| WEEK('2020-04-26') |+--------------------+| 17 |+--------------------+
Note that in the session where this query was run, the default mode was set to 0.
Mode parameter
To return week number for April 26, 2020 according to mode 4:
SELECT WEEK('2020-04-26', 4);
+-----------------------+| WEEK('2020-04-26', 4) |+-----------------------+| 18 |+-----------------------+
According to mode 4, April 26, 2020 is considered the 18th week of the year.
To return week number for April 26, 2020 according to mode 5:
SELECT WEEK('2020-04-26', 5);
+-----------------------+| WEEK('2020-04-26', 5) |+-----------------------+| 16 |+-----------------------+
According to mode 5, April 26, 2020 is considered the 16th week of the year.
