How to use the MySQL DAYOFMONTH() function

The DAYOFMONTH() is a MySQL function that returns the day of the month for a given date. This can be useful for extracting the numeric day from a DATE or DATETIME value in a query.

Syntax

The syntax for the DAYOFMONTH() function is:

DAYOFMONTH(date)

Where date is a DATE or DATETIME value.

Examples

Here are some examples of using DAYOFMONTH() in MySQL:

SELECT DAYOFMONTH('2023-11-14');

-- Returns 14

This extracts the day of the month from the given date string ‘2023-11-14’.

SELECT NAME, DAYOFMONTH(birthday)
FROM users;

This query selects the name and numeric day of the month from the birthday column in the users table.

SELECT DATE_FORMAT(NOW(), '%d') = DAYOFMONTH(NOW());

-- Returns 1 (true)

Here we compare the day of month extracted from the CURRENT_DATE to the value returned by DAYOFMONTH().

SELECT DATE_ADD(NOW(), INTERVAL DAYOFMONTH(NOW()) DAY);

-- Returns 2023-11-30 00:00:00

This example uses DAYOFMONTH() to increment the current date by the current day number.

SELECT DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW())-1 DAY);

-- Returns 2023-11-13 00:00:00

Similarly, this subtracts the current day of the month minus one to get the previous day.

Other Date Functions

Other useful MySQL date functions include:

• DAYNAME() - Get weekday name from date
• DAYOFWEEK() - Get weekday index from 1 (Sunday) to 7 (Saturday)
• DAYOFYEAR() - Get numeric day of the year
• MONTH() - Get month number (1-12) from date
• MONTHNAME() - Get month name from date

So DAYOFMONTH() can be combined with these functions to extract or process elements of a date in MySQL.