MySQL Day Function

MySQL Day is one of the Date Functions, which is useful to return the day of the month range from 0 to 31. Or, this function returns the Day number from a given date or DateTime expression.

Let us see how to use this function to get the day Number from an expression with an example. The basic syntax of the MySQL Day function is as shown below:

DAY(date or expression);

MySQL Day function Example

The below shown queries help you understand the basic use of this MySQL Day function. Here, we are returning the day number of the month from the date expression and the Datetime expression.

TIP: This Date method returns 0 if the argument is 0000-00-00 or 2019-00-00.

SELECT DAY('2016-11-25');

SELECT DAY('2018-10-25 01:14:22');

Let us see another example of this method. Here, we are extracting the number from the current date and time returned by the Now() function and the one returned by CURDATE().

SELECT DAY(NOW()), DAY(CURDATE());

MySQL Day Function Example 2

The following query shows you what happens when we try to extract the day number from a date in string format.

SELECT DAY(NOW() + 0);

SELECT DAY(NOW() + 0), DAY(CURDATE() + 12);

SELECT DAY(NOW() + 14);

From the above screenshot, (CURDATE() + 12); is returning NULL. Because Curdare() + 12 returns 20190231. This is the wrong date. Here, we are trying to extract the day number from a zero datepart. That’s why it returns NULL.

SELECT DAY('2019-00-00'), DAY('0');

Day Example 3

In this example, we show you how to use this MySQL Day function on a table. Here, we are finding or returning the Day number from the Hire Date column. For this demo, we are using Workbench to write a query against the customer database.

SELECT EmpID,
       FirstName,
       LastName,
       Occupation,
       YearlyIncome,
       Sales,
       HireDate,
       DAY(HireDate)
 FROM customer;