MySQL YEARWEEK() – Find the Year and Week Numbers from Date

YEARWEEK Function

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)

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;

And the output is –

MySQL YEARWEEK Basic Example

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;

And the output is –

MySQL YEARWEEK Datetime

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; 

And the output is –

MySQL YEARWEEK Substr

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;

And the output is –

Yearweek Curdate

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;

And the output is –

Yearweek Mode

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';

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';

And the output for both the above query sets are –

Yearweek Mode2

Working With Tables

Consider the below Employee table.

Yearweek 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;

And the output is –

Yearweek Table Example

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