Open In App

WEEKOFYEAR() Function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 24 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads