MySQL - DAYOFMONTH() Function

The MySQL DAYOFMONTH() function 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 31.

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

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

Syntax

Following is the syntax of MySQL DAYOFMONTH() function −

DAYOFMONTH(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 following query, we are using the MySQL DAYOFMONTH() function to extract the day of the month from the given date −

SELECT DAYOFMONTH('2019-05-25') As Result;

Output

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

Result
25

Example

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

SELECT DAYOFMONTH('2017-00-00') As Result;

Output

The output will be displayed as follows −

Result
0

Example

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

SELECT DAYOFMONTH('') As Result;

Output

The output will be displayed as follows −

Result
NULL

Example

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

SELECT DAYOFMONTH(CURDATE()) As Result;

Output

The output will be displayed as follows −

Result
11

Example

We can also pass the column name as an argument to this function. Let us create a table named ORDERS using CREATE 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:00 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 are using the MySQL DAYOFMONTH() function to extract the day part from the DATE column.

SELECT OID, DATE, DAYOFMONTH(DATE) From ORDERS;

Output

The output is displayed as follows −

OID DATE DAYOFMONTH(DATE)
102 2009-10-08 00:00:00 8
100 2009-10-08 00:00:00 8
101 2009-11-20 00:00:00 20
103 2008-05-20 00:00:00 20
Advertisements