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 specific date.

Introduction to MySQL DAY() function

The DAY() function returns the day of the month for a specific date.

Here’s the syntax of the DAY() function:

DAY(date);Code language: SQL (Structured Query Language) (sql)

The DAY() function accepts a DATE or DATETIME value for which you want to get the day of the month.

If the date is zero i.e.,'0000-00-00', the DAY() function returns 0. If 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.

1) Simple DAY() function example

The following example uses the DAY() function to get the day of the month of the date value 2010-01-15:

SELECT DAY('2010-01-15');Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------+
| DAY('2010-01-15') |
+-------------------+
|                15 |
+-------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

2) Using the DAY() function to get the number of days of a month

To get the number of days of a month based on a specified date, you use the combination of 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)

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 of a date, which is 2016-02-29 in this case.

The DAY() function returns the day number of the month of that last day of the month, which is equivalent to the number of days in the month.

3) Using the DAY() function with table data

We’ll use 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 2004:

SELECT 
  DAY(orderdate) dayofmonth, 
  COUNT(*) 
FROM 
  orders 
WHERE 
  YEAR(orderdate) = 2004 
GROUP BY 
  dayofmonth 
ORDER BY 
  dayofmonth;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+----------+
| dayofmonth | COUNT(*) |
+------------+----------+
|          1 |        5 |
|          2 |        9 |
|          3 |        7 |
|          4 |        8 |
|          5 |        6 |
|          6 |        3 |
|          7 |        4 |
|          8 |        4 |
|          9 |        7 |
|         10 |        7 |
|         11 |        3 |
|         12 |        5 |
|         13 |        3 |
|         14 |        3 |
|         15 |        9 |
|         16 |        8 |
|         17 |        5 |
|         18 |        3 |
|         19 |        6 |
|         20 |        8 |
|         21 |        6 |
|         22 |        5 |
|         23 |        2 |
|         24 |        5 |
|         25 |        2 |
|         26 |        4 |
|         27 |        2 |
|         28 |        2 |
|         29 |        6 |
|         30 |        4 |
+------------+----------+
30 rows in set (0.00 sec)Code language: JavaScript (javascript)

Summary

* Use the MySQL DAY() function to get the day of the month for a specific date.

Was this tutorial helpful?