In this tutorial, we will study the MySQL YEARWEEK() function. The MySQL YEARWEEK() function is a variation of the WEEK() function. The main difference is that, in addition to the week number, it also returns the year number. The output is in the format – YYYYWW; where YYYY indicates the year value and WW indicates the week number in that year. Let us take a look at the syntax and examples for it.
Syntax for MySQL YEARWEEK()
YEARWEEK(date, mode)Code language: SQL (Structured Query Language) (sql)Where ‘date’ is a date/datetime value to extract the year and week number from and,
‘mode’ is an optional parameter that specifies what day a week starts on. The value of mode can be from 0 to 7. By default, if not specified, this value is considered as 0.
Below is a list which specifies what each value of the ‘mode’ parameter means –
- For mode value ‘0’, the first day of the week is a Sunday and the Week 1 is the first week with a Sunday in that year. The value returned by YEARWEEK() is a value between 0 to 53.
- For mode value ‘1’, the first day of the week is a Monday, and the Week 1 is the first week with 4 or more days in that year. The value returned by MySQL YEARWEEK() is a value between 0 to 53.
- For mode value ‘2’, the first day of the week is a Sunday and the Week 1 is the first week with a Sunday in that year. The value returned by YEARWEEK() is a value between 1 to 53.
- For mode value ‘3’, the first day of the week is a Monday and the Week 1 is the first week with 4 or more days in that year. The value returned by YEARWEEK() is a value between 1 to 53.
- For mode value ‘4’, the first day of the week is a Sunday and the Week 1 is the first week with 4 or more days in that year. The value returned by MySQL YEARWEEK() is a value between 0 to 53.
- For mode value ‘5’, the first day of the week is a Monday and the Week 1 is the first week with a Monday in that year. The value returned by YEARWEEK() is a value between 0 to 53.
- For mode value ‘6’, the first day of the week is a Sunday and the Week 1 is the first week with 4 or more days in that year. The value returned by YEARWEEK() is a value between 1 to 53.
- For mode value ‘7’, the first day of the week is a Monday and the Week 1 is the first week with a Monday in that year. The value returned by YEARWEEK() is a value between 1 to 53.
It is very important to note that, for mode values where “Week 1 is the first week with 4 or more days in that year”, weeks are numbered according to ISO 8601:1988.
That means, 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 (week 52 or 53), and the next week is week 1.
Examples of MySQL YEARWEEK()
Let us take a look at a couple of basic examples. Let us find the year and week values for the dates – ‘2021-01-05’ and ‘2020-02-15’. The queries are –
SELECT YEARWEEK('2021-01-05') AS Result;
SELECT YEARWEEK('2020-02-15') AS Result;Code language: SQL (Structured Query Language) (sql)And the output is –
As you can see, the output is in the format YYYYWW; with the first four numbers indicating the year value and the last two numbers indicating the week value.
MySQL YEARWEEK() With Datetime Values
As I mentioned in the syntax, YEARWEEK() also works with datetime values. Let us see a couple of examples of this.
SELECT YEARWEEK('2020-12-31 23:59:59') AS Result;
SELECT YEARWEEK('2021-03-12 16:45:25') AS Result;Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL YEARWEEK() With SUBSTR()
Let us now use the SUBSTR() function and extract the year and week part separately from the result of the YEARWEEK(). Think of this as an exercise to implement YEAR() and WEEK() through the YEARWEEK() function. The queries are –
SELECT SUBSTR(YEARWEEK('2020-05-22'),4,2) AS Week;
SELECT SUBSTR(YEARWEEK('2020-05-22'),1,4) AS Year; Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL YEARWEEK() With CURDATE()
Let us see an example of the YEARWEEK() function with the CURDATE() function. The query is –
SELECT YEARWEEK(CURDATE()) AS Result;Code language: SQL (Structured Query Language) (sql)And the output is –
YEARWEEK() With The ‘mode’ Parameter
Let us now use the ‘mode’ parameter with the YEARWEEK() function. It behaves similar to that of the WEEK() function. Consider the below example.
SELECT YEARWEEK('2021-01-05', 3) AS Result;Code language: SQL (Structured Query Language) (sql)And the output is –
Let us gain more insight into the ‘mode’ parameter using two different date values.
For date ‘2020-01-05’ –
SET @date = '2020-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';Code language: SQL (Structured Query Language) (sql)For date ‘2020-01-02’ –
SET @date = '2020-01-02';
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';Code language: SQL (Structured Query Language) (sql)And the output for both the above query sets are –
Working With Tables
Consider the below Employee table.
Let us find the year and week using the YEARWEEK() function of the joining date of the employee under the alias – ‘Year Week Value’. Let us also display the employee id and name in the same query using the SELECT statement. The query is –
SELECT eid, Name, YEARWEEK(Date_Joined) AS 'Year Week Value' FROM Employee;Code language: SQL (Structured Query Language) (sql)And the output is –
Conclusion
The MySQL YEARWEEK() function is widely used while retrieving the year and week number of a time/datetime value. I would encourage you to practice different examples of it.
References
- MySQL Official Documentation on the
YEARWEEK()function.