In MySQL, the YEARWEEK() function returns the year and week for a given date. You provide the date as an argument, and the function will return the result accordingly.

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

You can use either of the following two forms:

YEARWEEK(date)
YEARWEEK(date,mode)

Where:

• date is the date you want the year and 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.

If no mode is specified, the mode is 0.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT YEARWEEK('2021-01-25') As 'Result';

Result:

+--------+
| Result |
+--------+
| 202104 |
+--------+

Here’s an example with a different date.

SELECT YEARWEEK('1999-12-25') As 'Result';

Result:

+--------+
| Result |
+--------+
| 199951 |
+--------+

Example 2 – Specify a Mode

If you don’t specify a second argument, the YEARWEEK() function uses 0 as the mode.

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

SELECT YEARWEEK('2019-11-23', 7) AS 'Mode 7';

Result:

+--------+
| Mode 7 |
+--------+
| 201946 |
+--------+

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

These are the same values that can be used with the WEEK() function.

One difference between these two functions is that the WEEK() function derives its default mode from the default_week_format system variable (the default value of this variable is 0). YEARWEEK() on the other hand, ignores this setting, and uses 0 as its default value (regardless of the default_week_format setting).

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 
  YEARWEEK(@date, 0) AS 'Mode 0',
  YEARWEEK(@date, 1) AS 'Mode 1',
  YEARWEEK(@date, 2) AS 'Mode 2',
  YEARWEEK(@date, 3) AS 'Mode 3',
  YEARWEEK(@date, 4) AS 'Mode 4',
  YEARWEEK(@date, 5) AS 'Mode 5',
  YEARWEEK(@date, 6) AS 'Mode 6',
  YEARWEEK(@date, 7) AS 'Mode 7';

Result:

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

Date 2

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

Result:

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

Date 3

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

Result:

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