MySQL DATEDIFF function is one of the Date methods, which is useful to find the difference between two dates and returns the number of days. Let us see how to use this Datediff function to find and return the number of days between two dates with an example.
The basic syntax of the MySQL DATEDIFF Function is as shown below:
DATEDIFF(Expression1, Expression2);
It subtracts Expression2 from Expression1 (Expression1 – Expression2) and returns the value. It won’t consider the Time value into consideration.
MySQL DATEDIFF function Example
The below shown queries help you understand the use of this Date Difference method. Here, we are finding the difference in the total number of days between the Datetime expression.
SELECT DATEDIFF('2019-02-28', '2019-01-01');
SELECT DATEDIFF('2019-02-28', '2019-12-31');
SELECT DATEDIFF('2019-02-28 12:11:22', '2019-12-31');
In this example, we are finding the difference between the Custom and the current datetime returned by the Now(), CURDATE, and CURRENT_TIMESTAMP.
SELECT NOW(), DATEDIFF(NOW(), '2018-12-31');
SELECT CURDATE(), DATEDIFF(CURDATE(), '2017-12-31');
SELECT CURRENT_TIMESTAMP, DATEDIFF(CURRENT_TIMESTAMP, '2018-01-31');
The Date Difference Example 2
In this example, we show you how to use this MySQL Date Difference function on a table. Here, we are finding the difference between the current and Employees HireDate using the datediff.
SELECT EmpID, FirstName, LastName, Occupation, YearlyIncome, Sales, HireDate, DATEDIFF(NOW(), HireDate) AS 'DATEDIFFExample' FROM customer;