MySQL Week Function

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.

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 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);
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);
WEEK Example 2

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);
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

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.