MySQL Week Date Function returns the Week number of the given date. This MySQL Week function accepts two arguments. The first one is the Date, and the second argument decides whether the Week starts on Sunday or Monday. And, the return value should be in the range of 0 to 53 or 1 to 53.
MySQL Week Function Syntax
The basic syntax of the MySQL Week Function is as shown below:
Week(date, Mode);
Here, Mode is an optional argument. The following table shows you the Mode argument return values. For example, if Mode= 0, then the week starts on Sunday, and the return value is between 0 and 53.
| Mode | First Day of the Week | Range | Week 1 is the First Week… |
|---|---|---|---|
| 0 | Sunday | 0 – 53 | Sunday in the Given Year |
| 1 | Monday | 0 – 53 | with 4 or more days this Year |
| 2 | Sunday | 1 – 53 | Sunday in the Given Year |
| 3 | Monday | 1 – 53 | with 4 or more days this Year |
| 4 | Sunday | 0 – 53 | with 4 or more days this Year |
| 5 | Monday | 0 – 53 | Monday in the Given Year |
| 6 | Sunday | 1 – 53 | with 4 or more days this Year |
| 7 | Monday | 1 – 53 | Monday in the Given Year |
MySQL Week function Example
The following examples help you understand the use of this MySQL week function. Here, we are returning the week number without, and with mode argument.
SELECT WEEK('2019-02-21');
SELECT WEEK('2019-02-21', 0);
SELECT WEEK('2019-02-21', 1);
Let us see another example of the Week Date method.
SELECT WEEK('2018-12-31', 1);
SELECT WEEK('2018-12-31', 2);
SELECT WEEK('2018-12-31', 3);
MySQL Week Function Example 2
Let us see another example of the Week function. This time, we are using a few more mode values and different MySQL dates.
SELECT WEEK('2018-10-31', 4);
SELECT WEEK('2018-05-24', 1);
SELECT WEEK('2018-02-02', 3);
The Week function returns 0 if the date falls on the last week of the previous week. To avoid this, you can use 2, 3, 6, or 7 as a Mode argument value.
SELECT WEEK('2018-01-01', 0);
SELECT WEEK('2018-01-01', 2);
SELECT WEEK('2018-01-01', 6);
