MySQL - WEEK() 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 WEEK() function is used to retrieve the calendar week of the given date. This function returns a numerical value, ranging from 1 to 53 or 1 to 53.

In addition to the date value this function accepts another optional parameter representing the mode using which you can specify whether the week should start on Sunday or Monday.

Following are the various possible values of the mode and the respective implications −

Mode value 0 − If the mode value is 0 first week of the day will be Sunday week values range is from 0 to 53 and the first week will be the week with a Sunday.
Mode value 1 − If the mode value is 1 first week of the day will be Monday week values range is from 0 to 53 and the first week will be the week with more than 4 days.
Mode value 2 − If the mode value is 2 first week of the day will be Sunday week values range is from 1 to 53 and the first week will be the week with a Sunday.
Mode value 3 − If the mode value is 3 first week of the day will be Monday week values range is from 1 to 53 and the first week will be the week with more than 4 days.
Mode value 4 − If the mode value is 4 first week of the day will be Sunday week values range is from 0 to 53 and the first week will be the week with more than 4 days.
Mode value 5 − If the mode value is 5 first week of the day will be Monday week values range is from 0 to 53 and the first week will be the week with a Monday.
Mode value 6 − If the mode value is 6 first week of the day will be Sunday week values range is from 1 to 53 and the first week will be the week with more than 4 days.
Mode value 7 − If the mode value is 7 first week of the day will be Monday week values range is from 1 to 53 and the first week will be the week with a Monday.

Syntax

Following is the syntax of the above function –

WEEK(date);

Example 1

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

mysql> SELECT WEEK('2019-05-25');
+--------------------+
| WEEK('2019-05-25') |
+--------------------+
|                 20 |
+--------------------+
1 row in set (0.06 sec)

Example 2

Following is another example of this function –

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

Example 3

Let us see the week of the date from above example for all the mode (optional parameter) values –

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

mysql> SELECT WEEK('2021-01-03', 2);
+-----------------------+
| WEEK('2021-01-03', 2) |
+-----------------------+
|                     1 |
+-----------------------+
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)

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

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

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

mysql> SELECT WEEK('2021-01-03', 7);
+-----------------------+
| WEEK('2021-01-03', 7) |
+-----------------------+
|                    52 |
+-----------------------+
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 WEEK('2017-00-00');
+--------------------+
| WEEK('2017-00-00') |
+--------------------+
|               NULL |
+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT WEEKDAY('1789-02-00');
+-----------------------+
| WEEKDAY('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 WEEK('');
+----------+
| WEEK('') |
+----------+
|     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 WEEK('2015-09-05 09:40:45.2300');
+----------------------------------+
| WEEK('2015-09-05 09:40:45.2300') |
+----------------------------------+
|                               35 |
+----------------------------------+
1 row in set (0.00 sec)

Example 7

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

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

Example 8

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

mysql> SELECT WEEK(CURRENT_TIMESTAMP());
+---------------------------+
| WEEK(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 number of all the entities of the Date_Of_Birth column of the table MyPlayers —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country,
WEEK(Date_Of_Birth) FROM MyPlayers;
+------------+------------+----------------+-------------+---------------------+
| First_Name | Last_Name  | Date_Of_Birth  | Country     | WEEK(Date_Of_Birth) |
+------------+------------+----------------+-------------+---------------------+
| Shikhar    | Dhawan     | 1981-12-05     | India       |                  48 |
| Jonathan   | Trott      | 1981-04-22     | SouthAfrica |                  16 |
| Kumara     | Sangakkara | 1977-10-27     | Srilanka    |                  43 |
| Virat      | Kohli      | 1988-11-05     | India       |                  44 |
| Rohit      | Sharma     | 1987-04-30     | India       |                  17 |
| 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 number from the DispatchTimeStamp column —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price,
WEEK(DispatchTimeStamp) FROM dispatches_data;
+-------------+--------------+---------------------+-------+-------------------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | WEEK(DispatchTimeStamp) |
+-------------+--------------+---------------------+-------+-------------------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  |                      17 |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  |                      25 |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  |                      48 |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  |                      11 |
| 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,
WEEK(SubscriptionTimeStamp) FROM SubscriberDetails;
+----------------+-------------+-----------------------+-----------------------------+
| SubscriberName | PackageName | SubscriptionTimeStamp | WEEK(SubscriptionTimeStamp) |
+----------------+-------------+-----------------------+-----------------------------+
| Ram            | Premium     | 2020-10-21 20:53:49   |                          42 |
| Rahman         | Basic       | 2020-11-26 10:13:19   |                          47 |
| Robert         | Moderate    | 2021-03-07 05:43:20   |                          10 |
| Radha          | Basic       | 2021-02-21 16:36:39   |                           8 |
| Rajiya         | Premium     | 2021-01-30 12:45:45   |                           4 |
+----------------+-------------+-----------------------+-----------------------------+
5 rows in set (0.00 sec)

Example 12

If the provided date’s week is containing Jan 1_st and has more than 4 days in the new year the result of this function will be 1.

mysql> SELECT WEEK('2012-01-01');
+--------------------+
| WEEK('2012-01-01') |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

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

Example 13

If the provided date date’s week is the last week of the previous year and the mode value is not 2, 3, 6, or 7 the result of this function will be 0.

mysql> SELECT WEEK('2015-01-01');
+--------------------+
| WEEK('2015-01-01') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK('2018-01-01');
+--------------------+
| WEEK('2018-01-01') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK('2018-01-03');
+--------------------+
| WEEK('2018-01-03') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql-date-time-functions.htm

Kickstart Your Career

Get certified by completing the course

Get Started