In this tutorial, we will study the MySQL WEEK() function. Every year has around 52 to 53 weeks. Suppose you want to find out the week number of a particular date. For this purpose, MySQL provides us with the WEEK() function. The WEEK() function is used to return the week number for a specified date. It is generally a number from 0 to 53. Let us take a look at the syntax and some examples.
Syntax of MySQL WEEK()
WEEK(date, mode)Code language: SQL (Structured Query Language) (sql)Where ‘date’ is a date/datetime value to extract the 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 WEEK() 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 WEEK() 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 WEEK() 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 WEEK() 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 WEEK() 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 WEEK() 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 WEEK() 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 WEEK() 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 WEEK()
Let us begin with a couple of basic examples. How about using the MySQL WEEK() function to find the week number of the dates – “2021-02-14” and “2020-12-30”? We will use the SELECT statement for this query. The query is –
SELECT WEEK("2021-02-14");
SELECT WEEK("2020-12-30");Code language: SQL (Structured Query Language) (sql)And we get the output as –
MySQL WEEK() With Datetime Value
As mentioned in the syntax, we can also pass a datetime value to the WEEK() function. Let us see an example of this. Consider the below query –
SELECT WEEK("2021-01-04 14:25:16");Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL WEEK() With CURDATE()
By now you must be curious as to how we find the week number of the current date. This is done by passing the CURDATE() function to the WEEK() function. The query is –
SELECT WEEK(CURDATE());Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL WEEK() Function With The ‘mode’ Parameter
Lastly, let us see an example of the WEEK() function with the ‘mode’ parameter. The query is –
SELECT WEEK("2021-02-14, 5");Code language: SQL (Structured Query Language) (sql)And the output is –
Wait, this hardly tells you anything at all. Let us take three dates – 5th January 2020, 7th January 2020 and 2nd January 2020. That way, the distinction between each mode value will be far more articulate for us. Consider the below queries –
For 5th January 2020 –
SET @date = '2020-01-05';
SELECT WEEK(@date, 0) AS 'Mode 0', WEEK(@date, 1) AS 'Mode 1',
WEEK(@date, 2) AS 'Mode 2', WEEK(@date, 3) AS 'Mode 3',
WEEK(@date, 4) AS 'Mode 4', WEEK(@date, 5) AS 'Mode 5',
WEEK(@date, 6) AS 'Mode 6', WEEK(@date, 7) AS 'Mode 7';Code language: SQL (Structured Query Language) (sql)For 7th January 2020 –
SET @date = '2020-01-07';
SELECT WEEK(@date, 0) AS 'Mode 0', WEEK(@date, 1) AS 'Mode 1',
WEEK(@date, 2) AS 'Mode 2', WEEK(@date, 3) AS 'Mode 3',
WEEK(@date, 4) AS 'Mode 4', WEEK(@date, 5) AS 'Mode 5',
WEEK(@date, 6) AS 'Mode 6', WEEK(@date, 7) AS 'Mode 7';Code language: SQL (Structured Query Language) (sql)For 2nd January 2020 –
SET @date = '2020-01-02';
SELECT WEEK(@date, 0) AS 'Mode 0', WEEK(@date, 1) AS 'Mode 1',
WEEK(@date, 2) AS 'Mode 2', WEEK(@date, 3) AS 'Mode 3',
WEEK(@date, 4) AS 'Mode 4', WEEK(@date, 5) AS 'Mode 5',
WEEK(@date, 6) AS 'Mode 6', WEEK(@date, 7) AS 'Mode 7';Code language: SQL (Structured Query Language) (sql)And the output is –
Take a look at the last example of 2nd January 2020 with mode 7. The week number is 52. You can google and see that 2nd January 2020 was a Thursday.
Now what does Mode 7 say?
It says “For mode value ‘7’, the first day of the week is a Monday, and Week 1 is the first week with a Monday in that year. The value returned by WEEK() is a value between 1 to 53.” This means every week in the year starts with a Monday and so should Week 1 for that year. But, since 2nd Jan 2020 is a Thursday and that means 1st Jan 2020 was a Wednesday, that means the week of 2nd Jan 2020 is counted as the last week of 2019 (the previous year). Hence, mode 7 for 2nd Jan 2020 results to week number 52.
Working with Tables
Consider the below Employee table.
Let us write a query that displays the employee id, their name, joining date and the week number of the joining date. The query is –
SELECT eid, Name, Date_Joined, WEEK(Date_Joined) AS WeekNumber FROM Employee;Code language: SQL (Structured Query Language) (sql)And the output is –
Conclusion
The MySQL WEEK() function is widely used while retrieving the week number of a time/datetime value. I would encourage you to practice different examples of it.
References
- MySQL Official Documentation on the
WEEK()function.