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 | +--------+--------+--------+--------+--------+--------+--------+--------+