YEARWEEK() Function in MySQL
Last Updated :
29 Sep, 2020
YEARWEEK() function in MySQL is used to find year and week for a given date. If the date is NULL, the YEARWEEK() function will return NULL. Otherwise, it returns value of year which range from 1000 to 9999 and value of week which ranges between 0 to 53.
Syntax :
YEARWEEK(date, mode)
Parameter : This method accepts two parameter as mentioned above and described below :
- date : The date or datetime from which we want to extract the year and week.
- mode : It specifies what day the week starts on. The following table describes how the mode argument works.
Mode |
First day of week |
Range |
Week 1 is the first week … |
0 |
Sunday |
0-53 |
with a Sunday in this year |
1 |
Monday |
0-53 |
with 4 or more days this year |
2 |
Sunday |
1-53 |
with a Sunday in this year |
3 |
Monday |
1-53 |
with 4 or more days this year |
4 |
Sunday |
0-53 |
with 4 or more days this year |
5 |
Monday |
0-53 |
with a Monday in this year |
6 |
Sunday |
1-53 |
with 4 or more days this year |
7 |
Monday |
1-53 |
with a Monday in this year |
Returns : It returns the value of year and week together.
Example-1 : Finding the Current Year and week Using Year() Function on 28/09/2020.
SELECT YEARWEEK(NOW()) AS Current_YearWeek;
Output :
+------------------+
| Current_YearWeek |
+------------------+
| 202039 |
+------------------+
1 row in set (0.00 sec)
So, the current year is 2020 and week number is 39.
Example-2 : Finding the Year and Week from given datetime Using YEARWEEK() Function.
SELECT YEARWEEK('2018-04-22 08:09:22') AS Year_Week ;
Output :
+-----------+
| Year_Week |
+-----------+
| 201816 |
+-----------+
So, the year is 2018 and week number is 16 in this example.
Example-3 : Finding the Year and Week from given datetime Using YEARWEEK() Function when the date is NULL.
SELECT YEARWEEK(NULL) AS Year_Week ;
Output :
+-----------+
| Year_Week |
+-----------+
| NULL |
+-----------+
Example-4 : In this example we are going to find number of student 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 Product table :
INSERT INTO
Course(Course_Name, Student_id, Student_name, Enroll_Date)
VALUES
( 'CS101', 161011, 'Amit Singh', '2019-11-26' ),
( 'CS101', 161029, 'Arun Kumar', '2019-11-30' ),
( 'CS101', 161031, 'Sanya Jain', '2019-12-08' ),
( 'CS101', 161058, 'Riya Shah', '2019-12-15' ),
( 'CS101', 162051, 'Amit Sharma', '2019-12-18' ),
( 'CS101', 161951, 'Sayan Singh', '2019-12-26' ),
( 'CS101', 167051, 'Rishi Jana', '2020-01-02' ),
( 'CS101', 168001, 'Aniket Dravid', '2020-01-10' ),
( 'CS101', 168051, 'Rita Singh', '2020-01-13' ),
( 'CS101', 166051, 'Kalyan Ghandi', '2020-01-26' ) ;
So, Our table looks like :
mysql> select * from Course;
+-------------+------------+---------------+-------------+
| Course_name | Student_id | Student_name | Enroll_Date |
+-------------+------------+---------------+-------------+
| CS101 | 161011 | Amit Singh | 2019-11-26 |
| CS101 | 161029 | Arun Kumar | 2019-11-30 |
| CS101 | 161031 | Sanya Jain | 2019-12-08 |
| CS101 | 161058 | Riya Shah | 2019-12-15 |
| CS101 | 161951 | Sayan Singh | 2019-12-26 |
| CS101 | 162051 | Amit Sharma | 2019-12-18 |
| CS101 | 166051 | Kalyan Ghandi | 2020-01-26 |
| CS101 | 167051 | Rishi Jana | 2020-01-02 |
| CS101 | 168001 | Aniket Dravid | 2020-01-10 |
| CS101 | 168051 | Rita Singh | 2020-01-13 |
+-------------+------------+---------------+-------------+
10 rows in set (0.00 sec)
Now, we are going to find number of student enrolled in the course for every week and year.
SELECT
YEARWEEK(Enroll_Date) YearandWeek,
COUNT(Student_id) Student_Enrolled
FROM
Course
GROUP BY YEARWEEK(Enroll_Date)
ORDER BY YEARWEEK(Enroll_Date);
Output :
+-------------+------------------+
| YearandWeek | Student_Enrolled |
+-------------+------------------+
| 201947 | 2 |
| 201949 | 1 |
| 201950 | 2 |
| 201951 | 1 |
| 201952 | 1 |
| 202001 | 1 |
| 202002 | 1 |
| 202004 | 1 |
+-------------+------------------+
8 rows in set (0.00 sec).
Similar Reads
YEAR() Function in MySQL
YEAR() function in MySQL is used to find year from the given date. If the date is NULL, the YEAR() function will return NULL. Otherwise, it returns value range from 1000 to 9999. Syntax : YEAR(date) Parameter : This method accepts one parameter as mentioned above and described below : date : The dat
3 min read
REPEAT() function in MySQL
REPEAT() : This function in MySQL is used to repeat a string a specified number of times. Syntax : REPEAT(str, count) Parameters : This method accepts two parameter. str -Input String which we want to repeat. count -It will describe that how many times to repeat the string. Returns : It returns a re
2 min read
SPACE() Function in MySQL
SPACE() function in MySQL is used to return a string consisting of specified empty space characters. Syntax : SPACE(num) Parameter : This method accepts one parameter as mentioned above and described below : num : It is an integer which indicates how many spaces are being contained. Returns : It ret
1 min read
SYSDATE() function in MySQL
SYSDATE() function in MySQL is used to return the current date and time in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu format depending on the context of the function. Syntax : SYSDATE() Parameter : This method does not accept any parameter. Returns : It returns the current date and time value. Exa
1 min read
POW() Function in MySQL
POW() function : This function in MySQL is used to return a results after raising a specified exponent number to a specified base number. For example if the base is 5 and exponent is 2, this will return a result of 25. Syntax : SELECT POW(x, y); Parameter : This method accepts two parameters as give
1 min read
TAN() Function in MySQL
TAN() function : This function in MySQL is used to return the tangent of a specified number. In any right triangle, the tangent of an angle is the length of the opposite side divided by the length of the adjacent side. Similarly, this can also be defined as tangent of x is the sine of x divided by t
1 min read
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 th
3 min read
TIME() Function in MySQL
The TIME() function in MySQL is used to extract the time portion from a date or datetime expression, returning the time in the format 'HH:MM'. This function is particularly useful when working with time components in databases, such as scheduling or logging systems. In this article, We will learn ab
4 min read
SUBDATE() function in MySQL
SUBDATE() function in MySQL is used to subtracts a time value (as interval) from a given date. Syntax : SUBDATE(date, INTERVAL expr unit) Parameter : This function accepts three parameters as given below : date : First specified date. expr : The value of the time/date interval to subtract. unit : Th
2 min read
QUOTE () function in MySQL
QUOTE() : This function in MySQL is used to return a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (\), single quote ('), ASCII NULL, and Control+Z preceded by a backslash. I
2 min read