MySQL Week Function 1

MySQL Week Function

MySQL Week Date Function returns the Week number of the given date. This Week function accepts two arguments. The first one is Date, and the second argument decides whether the Week starts with Sunday or Monday. And, the return value should be in range 0 to 53 or 1 to 53.

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.

ModeFirst Day of the WeekRangeWeek 1 is the First Week…
0Sunday0 – 53Sunday in the Given Year
1Monday0 – 53with 4 or more days this Year
2Sunday1 – 53Sunday in the Given Year
3Monday1 – 53with 4 or more days this Year
4Sunday0 – 53with 4 or more days this Year
5Monday0 – 53Monday in the Given Year
6Sunday1 – 53with 4 or more days this Year
7Monday1 – 53Monday in the Given Year

MySQL Week function Example

The following examples help you understand the use of this 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);
Get Week Number Example 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);
Example 2

Week 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);
MySQL Week Function 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);
MySQL Week Function 4