Summary: in this tutorial, you will learn how to use the MySQL DATEDIFF function to calculate the number of days between two date values.
MySQL DATEDIFF function syntax
MySQL DATEDIFF function calculate the number of days between two DATE, DATETIME, or TIMESTAMP values. The DATEDIFF function is useful in many cases e.g., you can calculate an interval in days that the products need to ship to a customer.
The syntax of the MySQL DATEDIFF function is as follows:
DATEDIFF(date_expression_1,date_expression_2)
The DATEDIFF function accepts two arguments which are two valid DATE, DATETIME or TIMESTAMP values. You can pass expressions that evaluate to DATE,DATETIME or TIMSTAMP values to the DATEDIFF function.
The DATEDIFF function returns the number of days between two DATE values i.e., date_expression_1 - date_expression_2.
Notice that the DATEDIFF function uses only date parts of the expressions for calculation. It ignores the time part if you pass the DATETIME or TIMESTAMP values.
MySQL DATEDIFF function examples
Let’s take a look at some examples of using the DATEDIFF function.
SELECT DATEDIFF('2011-08-17','2011-08-17'); -- 0 day SELECT DATEDIFF('2011-08-17','2011-08-08'); -- 9 days SELECT DATEDIFF('2011-08-08','2011-08-17'); -- -9 daysTo calculate the number of days between required date and shipped date for orders in the orders table, you use the DATEDIFF function as follows:
SELECT orderNumber, DATEDIFF(requiredDate,shippedDate) daysLeft FROM orders ORDER BY daysLeft DESC;
The following statement gets all orders whose statuses are in process and calculates the number of days between the ordered date and required date:
SELECT orderNumber, DATEDIFF(requiredDate,orderDate) remaining_days FROM orders WHERE status = 'In Process' ORDER BY remaining_days;
For calculating an interval in week or month, you can divide the returned value of the DATEDIFF function by 7 or 30 as the following query:
SELECT orderNumber, DATEDIFF(requiredDate,orderDate) /7, DATEDIFF(requiredDate,orderDate) /30 FROM orders WHERE status = 'In Process'
In this tutorial, you have learned how to use MySQL DATEDIFF function to calculate the number of days between two DATE or DATETIME values.