Tutorial Gateway

MySQL Day Function

by

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

In this article, we will show you, How to use this MySQL Day function to get the day Number from an expression with example.

MySQL Day Syntax

The basic syntax of the Day() Function in MySQL is as shown below:

DAY(date or expression);

TIP: This MySQL day function returns 0, if the date argument is 0000-00-00 or 2019-00-00.

MySQL Day function Example

The below shown queries will 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.

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

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

OUTPUT

MySQL Day Function 2

Let us see another example of MySQL 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 query will show 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 will show you, how to use this MySQL Day function on a table. Here, we are finding or returning Day number from the Hire Date column.

For this MySQL 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

Thank You for Visiting Our Blog