Open In App

WEEK() Function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 20 Oct, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads