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 DATEDIFF() function calculates the difference in days between two dates.

Here’s the basic syntax of the DATEDIFF() function:

DATEDIFF(end_date,start_date);

In this syntax:

• end_date: The date to which you want to calculate the difference.
• start_date: The date from which you want to calculate the difference.

The DATEDIFF() function returns an integer that represents the number of days between two dates.

If end_date or start_date is NULL, the DATEDIFF() function returns NULL.

Notice that the DATEDIFF() function considers only the date components for calculation and disregards the time components.

MySQL DATEDIFF examples

Let’s take some examples of using the DATEDIFF() function

1) Simple DATEDIFF() function example

The following example uses the DATEDIFF() function to calculate the difference between two DATE literal values:

SELECT 
  DATEDIFF('2011-08-17', '2011-08-17') days;Code language: SQL (Structured Query Language) (sql)

Try It Out

Output:

+------+
| days |
+------+
|    0 |
+------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following example uses the DATEDIFF() function to calculate the number of days between 2011-08-17 and 2011-08-08:

SELECT 
  DATEDIFF('2011-08-17', '2011-08-08') days;Code language: SQL (Structured Query Language) (sql)

Try It Out

Output:

+------+
| days |
+------+
|    9 |
+------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

2) Using DATEDIFF() function with table data

We’ll use the orders table in the sample database:

The following example uses the DATEDIFF() function to calculate the number of days between the required date and the shipped date of each order::

SELECT 
  orderNumber, 
  DATEDIFF(requiredDate, shippedDate) daysLeft 
FROM 
  orders 
ORDER BY 
  daysLeft DESC;Code language: SQL (Structured Query Language) (sql)

Try It Out

+-------------+----------+
| orderNumber | daysLeft |
+-------------+----------+
|       10409 |       11 |
|       10410 |       10 |
|       10105 |        9 |
|       10135 |        9 |
|       10190 |        9 |
|       10201 |        9 |
...

The following statement gets all orders whose statuses are in process and calculates the number of days between the ordered date and the required date:

SELECT 
  orderNumber, 
  DATEDIFF(requiredDate, orderDate) remainingDays 
FROM 
  orders 
WHERE 
  status = 'In Process' 
ORDER BY 
  remainingDays;Code language: SQL (Structured Query Language) (sql)

Try It Out

Output:

+-------------+---------------+
| orderNumber | remainingDays |
+-------------+---------------+
|       10423 |             6 |
|       10425 |             7 |
|       10421 |             8 |
|       10424 |             8 |
|       10420 |             9 |
|       10422 |            12 |
+-------------+---------------+
6 rows in set (0.00 sec)Code language: JavaScript (javascript)

3) Dealing with NULL values

The following example uses the DATEDIFF() function to calculate the days it takes from the order date to the shipped date:

SELECT 
  orderNumber, 
  DATEDIFF(shippedDate, orderDate) orderToShip 
FROM 
  orders 
ORDER BY 
  orderDate DESC;

Output:

+-------------+-------------+
| orderNumber | orderToShip |
+-------------+-------------+
|       10424 |        NULL |
|       10425 |        NULL |
|       10422 |        NULL |
|       10423 |        NULL |
|       10420 |        NULL |
|       10421 |        NULL |
|       10419 |           2 |
|       10418 |           4 |
|       10417 |           6 |
...Code language: PHP (php)

The following example uses the IFNULL() function to display N/A when the shipped date is NULL

SELECT 
  orderNumber, 
  IFNULL(DATEDIFF(shippedDate, orderDate), 'N/A') orderToShip 
FROM 
  orders 
ORDER BY 
  orderDate DESC;Code language: JavaScript (javascript)

Output:

+-------------+-------------+
| orderNumber | orderToShip |
+-------------+-------------+
|       10424 | N/A         |
|       10425 | N/A         |
|       10422 | N/A         |
|       10423 | N/A         |
|       10420 | N/A         |
|       10421 | N/A         |
|       10419 | 2           |
|       10418 | 4           |
|       10417 | 6           |
...

Summary

• Use MySQL DATEDIFF() function to calculate the number of days between two date values.