You can use the DAYOFMONTH() function in MySQL to return the day of the month from a date.

By “day of the month”, I mean a value between 1 and 31 (or 0 for dates with a zero day part), as opposed to the day of the week, such as Monday etc.

For example, if you provide a date of 2018-01-07, the DAYOFMONTH() function will return 7.

Syntax

The syntax goes like this:

DAYOFMONTH(date)

Where date is the date value that you want the day of the month returned from.

Example

Here’s an example to demonstrate.

SELECT DAYOFMONTH('2020-06-18') AS 'Result';

Result:

+--------+
| Result |
+--------+
|     18 |
+--------+

If the day part has a leading zero, the leading zero is omitted from the result. Example:

SELECT DAYOFMONTH('2020-06-07') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      7 |
+--------+

A Database Example

Here’s an example of extracting the day part from a column when running a query against a database.

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  DAYOFMONTH(payment_date) AS 'Day of Month'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+--------------+
| Date/Time           | Day of Month |
+---------------------+--------------+
| 2005-05-25 11:30:37 |           25 |
+---------------------+--------------+

Current Date/Time

Here’s an example of extracting the day part from the current date and time (which is returned using the NOW() function).

SELECT 
    NOW(),
    DAYOFMONTH(NOW());

Result:

+---------------------+-------------------+
| NOW()               | DAYOFMONTH(NOW()) |
+---------------------+-------------------+
| 2018-06-25 18:16:00 |                25 |
+---------------------+-------------------+

Another way to do this is to use the CURDATE() function, which returns only the date (but not the time).

SELECT 
    CURDATE(),
    DAYOFMONTH(CURDATE());    

Result:

+------------+-----------------------+
| CURDATE()  | DAYOFMONTH(CURDATE()) |
+------------+-----------------------+
| 2018-06-25 |                    25 |
+------------+-----------------------+