MySQL Day Function 3

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 MySQL function returns the Day number from a given date or DateTime expression.

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

DAY(date or expression);

MySQL Day function Example

The below shown queries help you understand the basic use of this 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');
MySQL Day Function 2

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());
Day Example 3

MySQL Day Function Example 2

The following MySQL 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);
Day Method Example 4

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');
MySQL Day Function 5

Day Example 3

In this example, we show you how to use this Day function on a table. Here, we are finding or returning 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;
MySQL Day Function 6