In MySQL, the DAY()
function is a synonym for the DAYOFMONTH()
function. It’s used to return the day of the month from a date.
In this context the “day of the month” is a value between 1 and 31, or 0 for dates with a zero day part. For example, if you provide a date of 2020-05-03, the DAY()
function will return 3.
Syntax
The syntax goes like this:
DAY(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 DAY('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 DAY('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', DAY(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(), DAY(NOW());
Result:
+---------------------+------------+ | NOW() | DAY(NOW()) | +---------------------+------------+ | 2018-06-25 18:30:44 | 25 | +---------------------+------------+
Another way to do this is to use the CURDATE()
function, which returns only the date (but not the time).
SELECT CURDATE(), DAY(CURDATE());
Result:
+------------+----------------+ | CURDATE() | DAY(CURDATE()) | +------------+----------------+ | 2018-06-25 | 25 | +------------+----------------+