MySQL YEARWEEK() Function
The MySQL YEARWEEK() function returns the year value as well as the week value of a given date or datetime value.
The returned year value can be different from year displayed in the datetime because of the mode specified. This happens in the first and last week of the year. Similarly, the week value can be between 0-53 or 1-53 depending on the specified mode.
Syntax
YEARWEEK(datetime, mode)
Parameters
datetime |
Required. Specify a date or datetime value from which to extract the year and week. |
mode |
Optional. Specify what day the week starts on. It can take value between 0-7. See the table below for description: |
mode and description
| mode | Description | Returns |
|---|---|---|
| 0 | First day of the week is Sunday | 0-53 |
| 1 | First day of the week is Monday and the first week has more than 3 days | 0-53 |
| 2 | First day of the week is Sunday | 1-53 |
| 3 | First day of the week is Monday and the first week has more than 3 days | 1-53 |
| 4 | First day of the week is Sunday and the first week has more than 3 days | 0-53 |
| 5 | First day of the week is Monday | 0-53 |
| 6 | First day of the week is Sunday and the first week has more than 3 days | 1-53 |
| 7 | First day of the week is Monday | 1-53 |
Note: If mode argument is omitted, the value of default_week_format system variable is used.
Return Value
Returns the year and week for a given date or datetime value.
Example 1:
The example below shows the usage of YEARWEEK() function.
mysql> SELECT YEARWEEK('2018-08-18');
Result: 201832
mysql> SELECT YEARWEEK('2018-08-18 10:38:42');
Result: 201832
mysql> SELECT YEARWEEK('2018-08-18 10:38:42.000004');
Result: 201832
mysql> SELECT YEARWEEK('2014-10-20');
Result: 201442
mysql> SELECT YEARWEEK(CURDATE());
Result: 202148
Example 2:
Consider a database table called Orders with the following records:
| OrderQuantity | Price | OrderTime |
|---|---|---|
| 100 | 1.58 | 2017-08-18 10:38:42 |
| 120 | 1.61 | 2018-03-23 07:14:16 |
| 125 | 1.78 | 2018-09-12 05:25:56 |
| 50 | 1.80 | 2019-01-16 11:52:05 |
| 200 | 1.72 | 2020-02-06 09:31:34 |
The statement given below can be used to get the year and week for records of column OrderTime:
SELECT *, YEARWEEK(OrderTime, 5) AS YEARWEEK_Value FROM Orders;
This will produce the result as shown below:
| OrderQuantity | Price | OrderTime | YEARWEEK_Value |
|---|---|---|---|
| 100 | 1.58 | 2017-08-18 10:38:42 | 201733 |
| 120 | 1.61 | 2018-03-23 07:14:16 | 201812 |
| 125 | 1.78 | 2018-09-12 05:25:56 | 201837 |
| 50 | 1.80 | 2019-01-16 11:52:05 | 201902 |
| 200 | 1.72 | 2020-02-06 09:31:34 | 202005 |
❮ MySQL Functions
