MySQL DATEDIFF Function

 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 the 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);

The DATEDIFF the 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 the 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 day

SELECT DATEDIFF('2011-08-17','2011-08-08'); --  9 days

SELECT DATEDIFF('2011-08-08','2011-08-17'); -- -9 days

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;

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,
    ROUND(DATEDIFF(requiredDate, orderDate) / 7, 2),
    ROUND(DATEDIFF(requiredDate, orderDate) / 30,2)
FROM
    orders
WHERE
    status = 'In Process';

Note that the ROUND the 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.