{"id":2700,"date":"2018-06-25T01:32:08","date_gmt":"2018-06-25T08:32:08","guid":{"rendered":"https:\/\/database.guide\/?p=2700"},"modified":"2021-01-28T14:11:20","modified_gmt":"2021-01-28T04:11:20","slug":"day-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/day-examples-mysql\/","title":{"rendered":"DAY() Examples – MySQL"},"content":{"rendered":"

In MySQL<\/a>, the DAY()<\/code> function is a synonym for the DAYOFMONTH()<\/code><\/a> function. It’s used to return the day of the month from a date.<\/p>\n

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<\/em>, the DAY()<\/code> function will return 3<\/em>.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

DAY(date)\r\n<\/pre>\n
Where date<\/code> is the date value that you want the day of the month returned from.<\/p>\n
Example<\/h2>\n
Here’s an example to demonstrate.<\/p>\n
SELECT DAY('2020-06-18') AS 'Result';\r\n<\/pre>\n
Result:<\/p>\n
+--------+\r\n| Result |\r\n+--------+\r\n|     18 |\r\n+--------+\r\n<\/pre>\n
If the day part has a leading zero, the leading zero is omitted from the result. Example:<\/p>\n
SELECT DAY('2020-06-07') AS 'Result';\r\n<\/pre>\n
Result:<\/p>\n
+--------+\r\n| Result |\r\n+--------+\r\n|      7 |\r\n+--------+\r\n<\/pre>\n
A Database Example<\/h2>\n
Here’s an example of extracting the day part from a column when running a query against a database.<\/p>\n
USE sakila;\r\nSELECT\r\n  payment_date AS 'Date\/Time',\r\n  DAY(payment_date) AS 'Day of Month'\r\nFROM payment\r\nWHERE payment_id = 1;\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+--------------+\r\n| Date\/Time           | Day of Month |\r\n+---------------------+--------------+\r\n| 2005-05-25 11:30:37 |           25 |\r\n+---------------------+--------------+\r\n<\/pre>\n
Current Date\/Time<\/h2>\n
Here’s an example of extracting the day part from the current date and time (which is returned using the NOW()<\/code><\/a> function).<\/p>\n
SELECT \r\n    NOW(),\r\n    DAY(NOW());\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+------------+\r\n| NOW()               | DAY(NOW()) |\r\n+---------------------+------------+\r\n| 2018-06-25 18:30:44 |         25 |\r\n+---------------------+------------+\r\n<\/pre>\n
Another way to do this is to use the CURDATE()<\/code><\/a> function, which returns only the date (but not the time).<\/p>\n
SELECT \r\n    CURDATE(),\r\n    DAY(CURDATE());    \r\n<\/pre>\n
Result:<\/p>\n
+------------+----------------+\r\n| CURDATE()  | DAY(CURDATE()) |\r\n+------------+----------------+\r\n| 2018-06-25 |             25 |\r\n+------------+----------------+\r\n<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[68,115,93],"class_list":["post-2700","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-date-functions","tag-dates","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2700","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2700"}],"version-history":[{"count":1,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2700\/revisions"}],"predecessor-version":[{"id":2701,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2700\/revisions\/2701"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2700"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2700"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2700"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}