In MySQL, you can use the WEEK() function to get the week number for a given date. By “week number” I mean the week of the year.

To use the function, simply provide the date as an argument and the week number will be returned.

You also have the option of specifying whether to start the week on Sunday or Monday, and whether the week should be in the range 0 to 53 or 1 to 53.

Syntax

The syntax goes like this:

WEEK(date[,mode])

Where:

• date is the date you want the week number returned from.
• mode is a number that specifies whether the week should start on Sunday or Monday and whether the week should be in the range 0 to 53 or 1 to 53. See the table below for the possible mode values.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT WEEK('2021-01-25') As 'Week Number';

Result:

+-------------+
| Week Number |
+-------------+
|           4 |
+-------------+

Here’s an example with a date near the end of the year.

SELECT WEEK('2021-12-25') As 'Week Number';

Result:

+-------------+
| Week Number |
+-------------+
|          51 |
+-------------+

Example 2 – Specify a Mode

If you don’t specify a second argument, the WEEK() function uses the mode as specified by the default_week_format system variable. The default value of this variable is 0.

However, you also have the option of supplying a second argument to specify which mode to use. Example:

SELECT WEEK('2019-10-17', 7) AS 'Mode 7';

Result:

+--------+
| Mode 7 |
+--------+
|     41 |
+--------+

The possible mode values are as follows.

Mode	First day of week	Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this 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	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year

For mode values where week 1 is the first week “with 4 or more days this year”, weeks are numbered according to ISO 8601:1988:

• If the week containing January 1 has 4 or more days in the new year, it is week 1.
• Otherwise, it is the last week of the previous year, and the next week is week 1.

Example 3 – Comparison of Modes

Here’s a quick comparison of how you can get different results depending on the mode being used.

The following three examples use the same code, but with three different dates. These dates are consecutive – they occur on the 5th, 6th, and 7th of January. As you can see, the results can be quite different depending on the exact date and the mode being used.

Date 1

SET @date = '2019-01-05';
SELECT 
  WEEK(@date, 0) AS 'Mode 0',
  WEEK(@date, 1) AS 'Mode 1',
  WEEK(@date, 2) AS 'Mode 2',
  WEEK(@date, 3) AS 'Mode 3',
  WEEK(@date, 4) AS 'Mode 4',
  WEEK(@date, 5) AS 'Mode 5',
  WEEK(@date, 6) AS 'Mode 6',
  WEEK(@date, 7) AS 'Mode 7';

Result:

+--------+--------+--------+--------+--------+--------+--------+--------+
| Mode 0 | Mode 1 | Mode 2 | Mode 3 | Mode 4 | Mode 5 | Mode 6 | Mode 7 |
+--------+--------+--------+--------+--------+--------+--------+--------+
|      0 |      1 |     52 |      1 |      1 |      0 |      1 |     53 |
+--------+--------+--------+--------+--------+--------+--------+--------+

Date 2

SET @date = '2019-01-06';
SELECT 
  WEEK(@date, 0) AS 'Mode 0',
  WEEK(@date, 1) AS 'Mode 1',
  WEEK(@date, 2) AS 'Mode 2',
  WEEK(@date, 3) AS 'Mode 3',
  WEEK(@date, 4) AS 'Mode 4',
  WEEK(@date, 5) AS 'Mode 5',
  WEEK(@date, 6) AS 'Mode 6',
  WEEK(@date, 7) AS 'Mode 7';

Result:

+--------+--------+--------+--------+--------+--------+--------+--------+
| Mode 0 | Mode 1 | Mode 2 | Mode 3 | Mode 4 | Mode 5 | Mode 6 | Mode 7 |
+--------+--------+--------+--------+--------+--------+--------+--------+
|      1 |      1 |      1 |      1 |      2 |      0 |      2 |     53 |
+--------+--------+--------+--------+--------+--------+--------+--------+

Date 3

SET @date = '2019-01-07';
SELECT 
  WEEK(@date, 0) AS 'Mode 0',
  WEEK(@date, 1) AS 'Mode 1',
  WEEK(@date, 2) AS 'Mode 2',
  WEEK(@date, 3) AS 'Mode 3',
  WEEK(@date, 4) AS 'Mode 4',
  WEEK(@date, 5) AS 'Mode 5',
  WEEK(@date, 6) AS 'Mode 6',
  WEEK(@date, 7) AS 'Mode 7';

Result:

+--------+--------+--------+--------+--------+--------+--------+--------+
| Mode 0 | Mode 1 | Mode 2 | Mode 3 | Mode 4 | Mode 5 | Mode 6 | Mode 7 |
+--------+--------+--------+--------+--------+--------+--------+--------+
|      1 |      2 |      1 |      2 |      2 |      1 |      2 |      1 |
+--------+--------+--------+--------+--------+--------+--------+--------+