MySQL DATEDIFF Function
Summary: in this tutorial, you will learn how to use the MySQL DATEDIFF function to calculate the number of days between two date values.
Introduction to MySQL DATEDIFF function
The MySQL DATEDIFF function calculates the number of days between two DATE, DATETIME, or TIMESTAMP values.
The syntax of the MySQL DATEDIFF function is as follows:
DATEDIFF(date_expression_1,date_expression_2);Code language: SQL (Structured Query Language) (sql)
The DATEDIFF function accepts two arguments that can be any valid date or date-time values. If you pass DATETIME or TIMESTAMP values, the DATEDIFF function only takes the date parts for calculation and ignores the time parts.
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.
MySQL DATEDIFF examples
Let’s take a look at some examples of using the DATEDIFF function.
SELECT DATEDIFF('2011-08-17','2011-08-17'); -- 0 dayCode language: SQL (Structured Query Language) (sql)
SELECT DATEDIFF('2011-08-17','2011-08-08'); -- 9 days
Code language: SQL (Structured Query Language) (sql)
SELECT DATEDIFF('2011-08-08','2011-08-17'); -- -9 daysCode language: SQL (Structured Query Language) (sql)
See the following orders table in the sample database.
To calculate the number of days between the required date and shipped date of the orders, you use the DATEDIFF function as follows:
SELECT
orderNumber,
DATEDIFF(requiredDate, shippedDate) daysLeft
FROM
orders
ORDER BY daysLeft DESC;Code language: SQL (Structured Query Language) (sql)
The following statement gets all orders whose statuses are in-process and calculates the number of days between ordered date and required date:
SELECT
orderNumber,
DATEDIFF(requiredDate, orderDate) remaining_days
FROM
orders
WHERE
status = 'In Process'
ORDER BY remaining_days;Code language: SQL (Structured Query Language) (sql)
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,
ROUND(DATEDIFF(requiredDate, orderDate) / 7, 2),
ROUND(DATEDIFF(requiredDate, orderDate) / 30,2)
FROM
orders
WHERE
status = 'In Process';Code language: SQL (Structured Query Language) (sql)
Note that the ROUND function is used to round the results.
In this tutorial, you have learned how to use MySQL DATEDIFF function to calculate the number of days between two date values.