MySQL DAY Function

 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() the function returns the day of the month of a given date. The following shows the syntax of the DAY function:

DAY(date);

The DAY() the 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() the 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');

Here is the output:

+-------------------+
| DAY('2010-01-15') |
+-------------------+
|                15 |
+-------------------+
1 row in set (0.00 sec)

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'));

The following is the output:

+-----------------------------+
| DAY(LAST_DAY('2016-02-03')) |
+-----------------------------+
|                          29 |
+-----------------------------+
1 row in set (0.00 sec)

In this example, the LAST_DAY() the function returns the last day of the month e.g. 2016-02-29, and the DAY() the function returns the day of the month of that last day which results in the number of days in the month.

C) Using MySQL DAY() function with a table example

See the following orders the 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;

In this tutorial, you have learned how to use the MySQL DAY() function to get the day of the month of a given date.