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 :

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:

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:

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

SELECT WEEKOFYEAR(NULL) 
AS Week_Number;

Output:

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.

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:

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql