Tutorial Gateway

MySQL Day Function

by

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

Let us see how to use this MySQL Day function to get the day Number from an expression with example. The basic syntax of the Day() Function in MySQL 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 or day of the month from the date expression, and the Date and time expression.

TIP: The day Date Function returns 0 if the date argument is 0000-00-00 or 2019-00-00.

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

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

OUTPUT

MySQL Day Function 2

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

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

OUTPUT

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

OUTPUT

MySQL Day Function 4

From the above screenshot, DAY(CURDATE() + 12); is returning NULL. Because Curdare() + 12 returns 20190231. This is a wrong date.

Here, we are trying to extract the day number from a zero date part. That’s why it returns NULL.

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

OUTPUT

MySQL Day Function 5

MySQL 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 Day function demo, we are using Workbench to write a query against the customer database.

SELECT EmpID,
       FirstName,
       LastName,
       Occupation,
       YearlyIncome,
       Sales,
       HireDate,
       DAY(HireDate)
 FROM `MySQL Tutorial`.customer;

OUTPUT

MySQL Day Function 6