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.

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

My Personal Notes arrow_drop_up

Save