MySQL - DAY() Function

The MySQL DAY() function is a synonym for DAYOFMONTH() function and 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 31.

When we say the day of month here, it is the 'DD' part of the 'DD/MM/YYYY' date format. The returns values of this function would only be within the range 1 to 31.

This function will return NULL if no argument or an empty string is passed to the it.

Syntax

Following is the syntax of MySQL DAY() function −

DAY(date);

Parameters

This method accepts the date value from which we need to get the day of the month as a parameter.

Return value

This function returns the day of the month from the provided date.

Example

In the below query, we are using the MySQL DAY() function to fetch the day of the month from the date "2023-11-11" −

Select DAY('2023-11-11') As Result;

Output

This will produce the following result −

Result
11

Example

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

SELECT DAY('') As Result;

This will produce the following result −

Result
NULL

Here, we are passing a non-string value as an argument to this function.

SELECT DAY(1990-11-11) As Result;

The output for the query above is produced as given below −

Result
NULL

Example

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

SELECT DAY('2015-09-05 09:40:45.2300') As Result;

Output

The output for the query above is produced as given below −

Result
5

Example

In the following example, we are retrieving the day values from the current date using CURDATE() with DAY() function −

SELECT DAY(CURDATE()) As Result;

Output

The output for the query above is produced as given below −

Result
5

Example

We can also pass the column name as an argument to this function. To do so, let us create a table named ORDERS using CREATE TABLE statement as shown below −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:0 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we use the MySQL DAY() function to retrieve the the day of the week corresponding to each order date from the "ORDERS" table.

Select OID, DATE, DAY(DATE) As Result From ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 8
100 2009-10-08 00:00:0 8
101 2009-11-20 00:00:00 20
103 2008-05-20 00:00:00 20
Advertisements