SQL Tutorial SQL Advanced SQL Database SQL References

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

modeDescriptionReturns
0First day of the week is Sunday0-53
1First day of the week is Monday and the first week has more than 3 days0-53
2First day of the week is Sunday1-53
3First day of the week is Monday and the first week has more than 3 days1-53
4First day of the week is Sunday and the first week has more than 3 days0-53
5First day of the week is Monday0-53
6First day of the week is Sunday and the first week has more than 3 days1-53
7First day of the week is Monday1-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:

OrderQuantityPriceOrderTime
1001.582017-08-18 10:38:42
1201.612018-03-23 07:14:16
1251.782018-09-12 05:25:56
501.802019-01-16 11:52:05
2001.722020-02-06 09:31:34

The query given below can be used to get the 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:

OrderQuantityPriceOrderTimeYEARWEEK_Value
1001.582017-08-18 10:38:42201733
1201.612018-03-23 07:14:16201812
1251.782018-09-12 05:25:56201837
501.802019-01-16 11:52:05201902
2001.722020-02-06 09:31:34202005

❮ MySQL Functions

5