Tutorial Gateway

MySQL Week Function

by

MySQL Week function is the Date Function, which is used to return the Week number of the given date. This function accepts two arguments. 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.

In this article, we will show you, How to use this Week function to get the Week number in MySQL with an example.

MySQL Week Syntax

The basic syntax of the Week() Function in MySQL is as shown below:

Week(date, Mode);

Here, Mode is an optional argument. The following table will show you the Mode argument return values. For example, if Mode = 0 then week starts on Sunday, and return value is between 0 and 53.

ModeFirst Day of the WeekRangeWeek 1 is the First Week…
0Sunday0 – 53Sunday in the Given Year
1Monday 0 – 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 helps you understand the use of this MySQL week function. Here, we are returning the week number without Mode argument, and with mode argument.

SELECT WEEK('2019-02-21');

SELECT WEEK('2019-02-21', 0);

SELECT WEEK('2019-02-21', 1);

OUTPUT

MySQL Week Function 1

Let us see another example of the MySQL Week function.

SELECT WEEK('2018-12-31', 1);

SELECT WEEK('2018-12-31', 2);

SELECT WEEK('2018-12-31', 3);

OUTPUT

MySQL Week Function 2

MySQL Week Example 2

Let us see another example of the MySQL Week function. This time, we are using few more mode values, and different dates.

SELECT WEEK('2018-10-31', 4);

SELECT WEEK('2018-05-24', 1);

SELECT WEEK('2018-02-02', 3);

OUTPUT

MySQL Week Function 3

MySQL Week function will return 0, if the date falls on 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);

OUTPUT

MySQL Week Function 4

Thank You for Visiting Our Blog