YEARWEEK() Function in MySQL
YEARWEEK() function in MySQL is used to find year and week for a given date. If the date is NULL, the YEARWEEK() function will return NULL. Otherwise, it returns value of year which range from 1000 to 9999 and value of week which ranges between 0 to 53.
Syntax :
YEARWEEK(date, mode)
Parameter : This method accepts two parameter as mentioned above and described below :
- date : The date or datetime from which we want to extract the year and week.
- mode : It specifies what day the week starts on. The following table describes how the mode argument works.
Mode | First day of week | Range | Week 1 is the first week … |
---|---|---|---|
0 | Sunday | 0-53 | with a Sunday in this year |
1 | Monday | 0-53 | with 4 or more days this year |
2 | Sunday | 1-53 | with a Sunday in this year |
3 | Monday | 1-53 | with 4 or more days this year |
4 | Sunday | 0-53 | with 4 or more days this year |
5 | Monday | 0-53 | with a Monday in this year |
6 | Sunday | 1-53 | with 4 or more days this year |
7 | Monday | 1-53 | with a Monday in this year |
Returns : It returns the value of year and week together.
Example-1 : Finding the Current Year and week Using Year() Function on 28/09/2020.
SELECT YEARWEEK(NOW()) AS Current_YearWeek;
Output :
+------------------+ | Current_YearWeek | +------------------+ | 202039 | +------------------+ 1 row in set (0.00 sec)
So, the current year is 2020 and week number is 39.
Example-2 : Finding the Year and Week from given datetime Using YEARWEEK() Function.
SELECT YEARWEEK('2018-04-22 08:09:22') AS Year_Week ;
Output :
+-----------+ | Year_Week | +-----------+ | 201816 | +-----------+
So, the year is 2018 and week number is 16 in this example.
Example-3 : Finding the Year and Week from given datetime Using YEARWEEK() Function when the date is NULL.
SELECT YEARWEEK(NULL) AS Year_Week ;
Output :
+-----------+ | Year_Week | +-----------+ | NULL | +-----------+
Example-4 : In this example we are going to find number of student enrolled in a course for every week in a year. To demonstrate create a table named.
Course :
CREATE TABLE Course( Course_name VARCHAR(100) NOT NULL, Student_id INT NOT NULL, Student_name VARCHAR(100) NOT NULL, Enroll_Date Date NOT NULL, PRIMARY KEY(Student_id) );
Now inserting some data to the Product table :
INSERT INTO Course(Course_Name, Student_id, Student_name, Enroll_Date) VALUES ( 'CS101', 161011, 'Amit Singh', '2019-11-26' ), ( 'CS101', 161029, 'Arun Kumar', '2019-11-30' ), ( 'CS101', 161031, 'Sanya Jain', '2019-12-08' ), ( 'CS101', 161058, 'Riya Shah', '2019-12-15' ), ( 'CS101', 162051, 'Amit Sharma', '2019-12-18' ), ( 'CS101', 161951, 'Sayan Singh', '2019-12-26' ), ( 'CS101', 167051, 'Rishi Jana', '2020-01-02' ), ( 'CS101', 168001, 'Aniket Dravid', '2020-01-10' ), ( 'CS101', 168051, 'Rita Singh', '2020-01-13' ), ( 'CS101', 166051, 'Kalyan Ghandi', '2020-01-26' ) ;
So, Our table looks like :
mysql> select * from Course; +-------------+------------+---------------+-------------+ | Course_name | Student_id | Student_name | Enroll_Date | +-------------+------------+---------------+-------------+ | CS101 | 161011 | Amit Singh | 2019-11-26 | | CS101 | 161029 | Arun Kumar | 2019-11-30 | | CS101 | 161031 | Sanya Jain | 2019-12-08 | | CS101 | 161058 | Riya Shah | 2019-12-15 | | CS101 | 161951 | Sayan Singh | 2019-12-26 | | CS101 | 162051 | Amit Sharma | 2019-12-18 | | CS101 | 166051 | Kalyan Ghandi | 2020-01-26 | | CS101 | 167051 | Rishi Jana | 2020-01-02 | | CS101 | 168001 | Aniket Dravid | 2020-01-10 | | CS101 | 168051 | Rita Singh | 2020-01-13 | +-------------+------------+---------------+-------------+ 10 rows in set (0.00 sec)
Now, we are going to find number of student enrolled in the course for every week and year.
SELECT YEARWEEK(Enroll_Date) YearandWeek, COUNT(Student_id) Student_Enrolled FROM Course GROUP BY YEARWEEK(Enroll_Date) ORDER BY YEARWEEK(Enroll_Date);
Output :
+-------------+------------------+ | YearandWeek | Student_Enrolled | +-------------+------------------+ | 201947 | 2 | | 201949 | 1 | | 201950 | 2 | | 201951 | 1 | | 201952 | 1 | | 202001 | 1 | | 202002 | 1 | | 202004 | 1 | +-------------+------------------+ 8 rows in set (0.00 sec).