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

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.