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)
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)
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)
+-------------+----------+
| 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)
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.