WEEK() Function in MySQL
WEEK() function in MySQL is used to find week number for a given date. If the date is NULL, the WEEK() function will return NULL. Otherwise, it returns the value of week which ranges between 0 to 53.
Syntax :
WEEK(date, mode)
Parameters : This method accepts two-parameters as mentioned above in the syntax and described below –
- date –
The date or datetime from which we want to extract the week. - mode –
It specifies what day the week starts on. The following table describes how the mode argument works.
Returns : It returns the value of the week number.
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 |
Example-1 :
Finding the Current week number Using WEEK() Function on 15/10/2020.
SELECT WEEK(NOW()) AS Current_Week;
Output :
Current_Week |
---|
41 |
So, the current week number is 41.
Example-2 :
Finding the Week from given datetime Using WEEK() Function.
SELECT WEEK('2010-05-20 08:09:22') AS Week;
Output :
Week |
---|
20 |
So, week number is 20 in this example.
Example-3 :
Finding the Week from given datetime Using WEEK() Function when the date is NULL.
SELECT WEEK(NULL) AS Week;
Output :
Week |
---|
NULL |
Example-4 :
In this example we are going to find number of student enrolled in a course for every week. 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 Course table –
INSERT INTO Course(Course_Name, Student_id, Student_name, Enroll_Date) VALUES ('CS101', 161011, 'Amit Singh', '2019-1-26'), ('CS101', 161029, 'Arun Kumar', '2019-5-30'), ('CS101', 161031, 'Sanya Jain', '2019-6-08'), ('CS101', 161058, 'Riya Shah', '2019-10-15'), ('CS101', 162051, 'Amit Sharma', '2019-10-18'), ('CS101', 161951, 'Sayan Singh', '2019-10-30'), ('CS101', 167051, 'Rishi Jana', '2019-11-02'), ('CS101', 168001, 'Aniket Dravid', '2019-11-10'), ('CS101', 168051, 'Rita Singh', '2019-11-13'), ('CS101', 166051, 'Kalyan Ghandi', '2019-12-26');
Table – Course
COURSE_NAME | STUDENT_ID | STUDENT_NAME | ENROLL_DATE |
---|---|---|---|
CS101 | 161011 | Amit Singh | 2019-1-26 |
CS101 | 161029 | Arun Kumar | 2019-5-30 |
CS101 | 161031 | Sanya Jain | 2019-6-08 |
CS101 | 161058 | Riya Shah | 2019-10-15 |
CS101 | 162051 | Amit Sharma | 2019-10-18 |
CS101 | 161951 | Sayan Singh | 2019-10-30 |
CS101 | 167051 | Rishi Jana | 2019-11-02 |
CS101 | 168001 | Aniket Dravid | 2019-11-10 |
CS101 | 168051 | Rita Singh | 2019-11-13 |
CS101 | 166051 | Kalyan Ghandi | 2019-12-26 |
Now, we are going to find number of student enrolled in the course for every week.
SELECT WEEK(Enroll_Date) WeekNumber, COUNT(Student_id) Student_Enrolled FROM Course GROUP BY WEEK(Enroll_Date) ORDER BY WEEK(Enroll_Date);
Output :
WEEKNUMBER | STUDENT_ENROLLED |
---|---|
3 | 1 |
21 | 1 |
22 | 1 |
41 | 2 |
43 | 2 |
45 | 2 |
51 | 1 |