Skip to content
Related Articles

Related Articles

Improve Article

WEEKOFYEAR() Function in MySQL

  • Last Updated : 05 Oct, 2020

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_NameStudent_idStudent_nameEnroll_Date
CS101161011Amit Singh2019-10-06
CS101161029Arun Kumar2019-10-23
CS101161031Sanya Jain2019-11-08
CS101161058Riya Shah2019-11-20
CS101162051Amit Sharma2019-11-30
CS101161951Sayan Singh2019-12-07
CS101167051Rishi Jana2019-12-15
CS101168001Aniket Dravid2019-12-25
CS101168051Rita Singh2019-12-28
CS101166051Kalyan Ghandi2019-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_NumberStudent_Enrolled
401
431
451
471
481
491
501
523
My Personal Notes arrow_drop_up
Recommended Articles
Page :