MySQL - DAY() 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 DAY() function is a synonym for DAYOFMONTH() function. It is used to retrieve the day of the month from the given date. This function returns the numerical value representing the day of the month which will be a number from 1 to 30.

Syntax

Following is the syntax of the above function –

DAY(date);

Where, date is the date value from which you need to get the day of the month.

or a non-string value as an argument this function returns NULL.

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

Example 1

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

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

Example 2

In the following example we are retrieving the day value from the current date —

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

Example 3

In the following example we are retrieving the day values from the current timestamp —

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

Example 4

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 day from all the entities of the Date_Of_Birth column of the table MyPlayers —

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

Example 5

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 month values from the DispatchTimeStamp column —

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

Example 6

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 day (of the month) of subscription for all the records —

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