WEEK() Function in MySQL

WEEK() function in MySQL is a versatile built-in date function designed to extract the week number from a given date. This function is particularly beneficial for grouping and analyzing data based on weekly intervals, allowing for more insightful data interpretation and reporting.

In this article, We will learn about the WEEK() Function in MySQL in detail by understanding various examples and so on.

WEEK() Function in MySQL

• The WEEK() function in MySQL is a built-in date function that returns the week number of a given date.
  
• It can be particularly useful for grouping or analyzing data based on weeks.

Syntax:

WEEK(date[, mode])

Parameters:

• date: The date value from which to extract the week number. This can be a date or datetime expression.
  
• mode (optional): An integer that specifies the mode for determining the first week of the year and the first day of the week. If not specified, the default mode is 0.

Examples of WEEK() Function in MySQL

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

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:

Conclusion

In summary, the WEEK() function serves as a powerful tool for deriving week numbers from dates in MySQL. Its ability to accommodate different modes for defining the start of the week and the first week of the year adds flexibility for users.