{"id":6233,"date":"2017-07-09T23:29:36","date_gmt":"2017-07-10T06:29:36","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6233"},"modified":"2023-10-15T22:21:07","modified_gmt":"2023-10-16T05:21:07","slug":"mysql-day","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-day\/","title":{"rendered":"MySQL DAY() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL DAY()<\/code> function to get the day of the month of a specific date.<\/p>\n\n\n\n

Introduction to MySQL DAY() function<\/h2>\n\n\n\n

The DAY()<\/code> function returns the day of the month for a specific date<\/a>. <\/p>\n\n\n\n

Here’s the syntax of the DAY()<\/code> function:<\/p>\n\n\n

DAY(date);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the date<\/code> is zero i.e.,'0000-00-00'<\/code>, the DAY()<\/code> function returns 0. If the date<\/code> is NULL<\/a><\/code>, the DAY()<\/code> function returns NULL<\/code>.<\/p>\n\n\n\n
Note that DAY()<\/code> function is the synonym of the DAYOFMONTH()<\/code> function.<\/p>\n\n\n\n
MySQL DAY() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the DAY()<\/code> function.<\/p>\n\n\n\n
1) Simple DAY() function example<\/h3>\n\n\n\n
The following example uses the DAY()<\/code> function to get the day of the month of the date value 2010-01-15<\/code>:<\/p>\n\n\n
SELECT<\/span> DAY<\/span>('2010-01-15'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-------------------+\n| DAY('2010-01-15'<\/span>) |\n+-------------------+\n|                15<\/span> |\n+-------------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using the DAY() function to get the number of days of a month<\/h3>\n\n\n\n
To get the number of days of a month based on a specified date, you use the combination of the LAST_DAY()<\/code> and DAY()<\/code> functions as shown in the following example:<\/p>\n\n\n
SELECT<\/span> DAY<\/span>(LAST_DAY<\/span>('2016-02-03'<\/span>));<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------------------------+\n| DAY(LAST_DAY('2016-02-03'<\/span>)) |\n+-----------------------------+\n|                          29<\/span> |\n+-----------------------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the LAST_DAY()<\/code> function returns the last day of the month of a date, which is  2016-02-29<\/code> in this case.  <\/p>\n\n\n\n
The DAY()<\/code> 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.<\/p>\n\n\n\n
3) Using the DAY() function with table data<\/h3>\n\n\n\n
We’ll use the following orders<\/code> table in the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following statement uses the DAY()<\/code> function to return the number of orders by day number in 2004:<\/p>\n\n\n
SELECT<\/span> \n  DAY<\/span>(orderdate) dayofmonth<\/span>, \n  COUNT<\/span>(*) \nFROM<\/span> \n  orders \nWHERE<\/span> \n  YEAR<\/span>(orderdate) = 2004<\/span> \nGROUP<\/span> BY<\/span> \n  dayofmonth<\/span> \nORDER<\/span> BY<\/span> \n  dayofmonth<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------+----------+\r\n| dayofmonth | COUNT(*) |\r\n+------------+----------+\r\n|          1<\/span> |        5<\/span> |\r\n|          2<\/span> |        9<\/span> |\r\n|          3<\/span> |        7<\/span> |\r\n|          4<\/span> |        8<\/span> |\r\n|          5<\/span> |        6<\/span> |\r\n|          6<\/span> |        3<\/span> |\r\n|          7<\/span> |        4<\/span> |\r\n|          8<\/span> |        4<\/span> |\r\n|          9<\/span> |        7<\/span> |\r\n|         10<\/span> |        7<\/span> |\r\n|         11<\/span> |        3<\/span> |\r\n|         12<\/span> |        5<\/span> |\r\n|         13<\/span> |        3<\/span> |\r\n|         14<\/span> |        3<\/span> |\r\n|         15<\/span> |        9<\/span> |\r\n|         16<\/span> |        8<\/span> |\r\n|         17<\/span> |        5<\/span> |\r\n|         18<\/span> |        3<\/span> |\r\n|         19<\/span> |        6<\/span> |\r\n|         20<\/span> |        8<\/span> |\r\n|         21<\/span> |        6<\/span> |\r\n|         22<\/span> |        5<\/span> |\r\n|         23<\/span> |        2<\/span> |\r\n|         24<\/span> |        5<\/span> |\r\n|         25<\/span> |        2<\/span> |\r\n|         26<\/span> |        4<\/span> |\r\n|         27<\/span> |        2<\/span> |\r\n|         28<\/span> |        2<\/span> |\r\n|         29<\/span> |        6<\/span> |\r\n|         30<\/span> |        4<\/span> |\r\n+------------+----------+\r\n30<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
* Use the MySQL DAY()<\/code> function to get the day of the month for a specific date.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
The DAY()<\/code> function accepts a DATE<\/a><\/code> or DATETIME<\/a><\/code> value for which you want to get the day of the month. <\/p>\n\n\n\n