WEEKOFYEAR() Function in MySQL

WEEKOFYEAR() function in MySQL is used to find the week number for a given date. If the date is NULL, the WEEKOFYEAR function will return NULL. Otherwise, it returns the value of week which ranges between 1 to 53. 

Note: The WEEKOFYEAR() function considers the first week of the year to be the week that contains the first day of January. Also, the week number returned by WEEKOFYEAR() depends on the value of the `sql_mode` system variable. By default, WEEKOFYEAR() uses the mode that complies with ISO 8601.

Syntax:

WEEKOFYEAR( date)

Parameter : This method accepts only one parameter.

• date –The date or datetime from which we want to extract the week number.

Returns : It returns the   week number. Example-1 : Finding the Current week number Using WEEKOFYEAR() Function on 29/09/2020.

SELECT WEEKOFYEAR(NOW()) AS Current_Week;

Output :

Current_Week
40

So, the current week number is 40. Example-2 : Finding the Week from given DateTime Using WEEKOFYEAR() Function.

SELECT WEEKOFYEAR('2018-04-22 08:09:22') 
AS Week_Number ;

Output:

Week_Number
16

So, the week number is 16 in this example. Example-3 : Finding the Week from given date Using WEEKOFYEAR() Function.

SELECT WEEKOFYEAR('2019-07-25 ') 
AS Week_Number ;

Output:

Week_Number
30

Example-4 : Finding the Week number from given datetime Using WEEKOFYEAR() Function  when the date is NULL.

SELECT WEEKOFYEAR(NULL) 
AS Week_Number;

Output:

Week_Number
NULL

Example-4: In this example, we are going to find the number of students 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 Course table.

INSERT INTO
Course(Course_Name, Student_id, Student_name, Enroll_Date)
VALUES
    ( 'CS101', 161011, 'Amit Singh', '2019-10-06' ),
    ( 'CS101', 161029, 'Arun Kumar', '2019-10-23' ),
    ( 'CS101', 161031, 'Sanya Jain', '2019-11-08' ),
    ( 'CS101', 161058, 'Riya Shah', '2019-11-20' ),
    ( 'CS101', 162051, 'Amit Sharma', '2019-11-30' ),
    ( 'CS101', 161951, 'Sayan Singh', '2019-12-07' ),
    ( 'CS101', 167051, 'Rishi Jana', '2019-12-15' ),
    ( 'CS101', 168001, 'Aniket Dravid', '2019-12-25' ),
    ( 'CS101', 168051, 'Rita Singh', '2019-12-28' ),
    ( 'CS101', 166051, 'Kalyan Ghandi', '2019-12-29' ) ;

So, Our table looks like.

Course_Name	Student_id	Student_name	Enroll_Date
CS101	161011	Amit Singh	2019-10-06
CS101	161029	Arun Kumar	2019-10-23
CS101	161031	Sanya Jain	2019-11-08
CS101	161058	Riya Shah	2019-11-20
CS101	162051	Amit Sharma	2019-11-30
CS101	161951	Sayan Singh	2019-12-07
CS101	167051	Rishi Jana	2019-12-15
CS101	168001	Aniket Dravid	2019-12-25
CS101	168051	Rita Singh	2019-12-28
CS101	166051	Kalyan Ghandi	2019-12-39

Now, we are going to find the number of students enrolled in the course every week.

SELECT
WEEKOFYEAR(Enroll_Date) Week_Number,
COUNT(Student_id) Student_Enrolled
FROM
    Course
    GROUP BY WEEKOFYEAR(Enroll_Date)
    ORDER BY WEEKOFYEAR(Enroll_Date);

Output:

Week_Number	Student_Enrolled
40	1
43	1
45	1
47	1
48	1
49	1
50	1
52	3