MySQL DAY Function
Summary: in this tutorial, you will learn how to use the MySQL DAY() function to get the day of the month of a specified date.
Introduction to MySQL DAY() function
The DAY() function returns the day of the month of a given date. The following shows the syntax of the DAY function:
DAY(date);
Code language: SQL (Structured Query Language) (sql)
The DAY() function accepts one argument that is a date value for which you want to get the day of the month. If the date argument is zero e.g., '0000-00-00', the DAY() function returns 0. In case the date is NULL, the DAY() function returns NULL.
Note that DAY() function is the synonym of the DAYOFMONTH() function.
MySQL DAY() function examples
Let’s take some examples of using the DAY() function.
A) MySQL DAY() function simple example
The following example returns the day of the month of 2010-01-15:
SELECT DAY('2010-01-15');Code language: SQL (Structured Query Language) (sql)
Here is the output:
+-------------------+
| DAY('2010-01-15') |
+-------------------+
| 15 |
+-------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)
B) Using MySQL DAY() function to get the number of days in a month of a date
To get the number of days in a month based on a specified date, you combine the LAST_DAY() and DAY() functions as shown in the following example:
SELECT DAY(LAST_DAY('2016-02-03'));Code language: SQL (Structured Query Language) (sql)
The following is the output:
+-----------------------------+
| DAY(LAST_DAY('2016-02-03')) |
+-----------------------------+
| 29 |
+-----------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)
In this example, the LAST_DAY() function returns the last day of the month e.g. 2016-02-29, and the DAY() function returns the day of the month of that last day that results in the number of days in the month.
C) Using MySQL DAY() function with a table example
See the following orders table in the sample database:
The following statement uses the DAY() function to return the number of orders by day number in 2003.
SELECT
DAY(orderdate) dayofmonth,
COUNT(*)
FROM
orders
WHERE
YEAR(orderdate) = 2004
GROUP BY dayofmonth
ORDER BY dayofmonth;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL DAY() function to get the day of the month of a given date.