MySQL - WEEKOFYEAR() Function

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL WEEKOFYEAR() function is used to retrieve the calendar week of the given date. This function returns a numerical ranging from 1 to 53.

Syntax

Following is the syntax of the above function –

WEEKOFYEAR(date);

Where, date is the date value from which you need to retrieve the week of the year.

Example 1

Following example demonstrates the usage of the WEEKOFYEAR() function –

mysql> SELECT WEEKOFYEAR('2019-05-25');
+--------------------------+
| WEEKOFYEAR('2019-05-25') |
+--------------------------+
|                       21 |
+--------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT WEEKOFYEAR('1998-11-21');
+--------------------------+
| WEEKOFYEAR('1998-11-21') |
+--------------------------+
|                       47 |
+--------------------------+
1 row in set (0.00 sec)

Example 3

This function is equivalent to WEEK(date, 3) –

mysql> SELECT WEEKOFYEAR('2021-01-03');
+--------------------------+
| WEEKOFYEAR('2021-01-03') |
+--------------------------+
|                       53 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK('2021-01-03', 3);
+-----------------------+
| WEEK('2021-01-03', 3) |
+-----------------------+
|                    53 |
+-----------------------+
1 row in set (0.00 sec)

Example 4

If the day part in the given date is 0 this function returns NULL —

mysql> SELECT WEEKOFYEAR('2017-00-00');
+--------------------------+
| WEEKOFYEAR('2017-00-00') |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT WEEKOFYEAR('1789-02-00');
+--------------------------+
| WEEKOFYEAR('1789-02-00') |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

Example 5

If you pass an empty string or a non-string value as an argument this function returns NULL.

mysql> SELECT WEEKOFYEAR('');
+----------------+
| WEEKOFYEAR('') |
+----------------+
|           NULL |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT WEEK(1990-11-11);
+------------------+
| WEEK(1990-11-11) |
+------------------+
|             NULL |
+------------------+
1 row in set, 1 warning (0.00 sec)

Example 6

We can also pass the date-time expression as an argument to this function –

mysql> SELECT WEEKOFYEAR('2015-09-05 09:40:45.2300');
+----------------------------------------+
| WEEKOFYEAR('2015-09-05 09:40:45.2300') |
+----------------------------------------+
|                                     36 |
+----------------------------------------+
1 row in set (0.00 sec)

Example 7

In the following example we are retrieving week of the year for the current date —

mysql> SELECT WEEKOFYEAR(CURDATE());
+-----------------------+
| WEEKOFYEAR(CURDATE()) |
+-----------------------+
|                    28 |
+-----------------------+
1 row in set (0.00 sec)

Example 8

In the following example we are retrieving the week of the year for the current timestamp —

mysql> SELECT WEEKOFYEAR(CURRENT_TIMESTAMP());
+---------------------------------+
| WEEKOFYEAR(CURRENT_TIMESTAMP()) |
+---------------------------------+
|                              28 |
+---------------------------------+
1 row in set (0.00 sec)

Example 9

You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);
Now, we will insert 7 records in MyPlayers table using INSERT statements:
mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query extracts the week of the year for all entities of the Date_Of_Birth column of the table MyPlayers —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country,
WEEKOFYEAR(Date_Of_Birth) FROM MyPlayers;
+------------+------------+---------------+-------------+---------------------------+
| First_Name | Last_Name  | Date_Of_Birth | Country     | WEEKOFYEAR(Date_Of_Birth) |
+------------+------------+---------------+-------------+---------------------------+
| Shikhar    | Dhawan     | 1981-12-05    | India       |                        49 |
| Jonathan   | Trott      | 1981-04-22    | SouthAfrica |                        17 |
| Kumara     | Sangakkara | 1977-10-27    | Srilanka    |                        43 |
| Virat      | Kohli      | 1988-11-05    | India       |                        44 |
| Rohit      | Sharma     | 1987-04-30    | India       |                        18 |
| Ravindra   | Jadeja     | 1988-12-06    | India       |                        49 |
| James      | Anderson   | 1982-06-30    | England     |                        26 |
+------------+------------+---------------+-------------+---------------------------+
7 rows in set (0.00 sec)

Example 10

Suppose we have created a table named dispatches_data with 5 records in it using the following queries –

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query retrieves the day of the week of the year from the DispatchTimeStamp column —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price,
WEEKOFYEAR(DispatchTimeStamp) FROM dispatches_data;
+-------------+--------------+---------------------+-------+-------------------------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | WEEKOFYEAR(DispatchTimeStamp) |
+-------------+--------------+---------------------+-------+-------------------------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  |                            18 |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  |                            26 |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  |                            49 |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  |                            12 |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  |                            52 |
+-------------+--------------+---------------------+-------+-------------------------------+
5 rows in set (0.00 sec)

Example 11

Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberDetails (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimeStamp timestamp
);
insert into SubscriberDetails values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49'));
insert into SubscriberDetails values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19'));
insert into SubscriberDetails values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20'));
insert into SubscriberDetails values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39'));
insert into SubscriberDetails values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45'));

Following query retrieves and displays the week of subscription year for all the users —

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp,
WEEKOFYEAR(SubscriptionTimeStamp) FROM SubscriberDetails;
+----------------+-------------+-----------------------+-----------------------------------+
| SubscriberName | PackageName | SubscriptionTimeStamp | WEEKOFYEAR(SubscriptionTimeStamp) |
+----------------+-------------+-----------------------+-----------------------------------+
| Ram            | Premium     | 2020-10-21 20:53:49   |                                43 |
| Rahman         | Basic       | 2020-11-26 10:13:19   |                                48 |
| Robert         | Moderate    | 2021-03-07 05:43:20   |                                 9 |
| Radha          | Basic       | 2021-02-21 16:36:39   |                                 7 |
| Rajiya         | Premium     | 2021-01-30 12:45:45   |                                 4 |
+----------------+-------------+-----------------------+-----------------------------------+
5 rows in set (0.00 sec)

mysql-date-time-functions.htm

Kickstart Your Career

Get certified by completing the course

Get Started