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 :
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 :
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 :
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 |
Last Updated :
20 Oct, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...