MySQL DATEDIFF() Function

The MySQL DATEDIFF() function returns the difference between two date/datetime values. The returned value is expressed in days and the time portion of date1 and date2 are ignored.

Syntax

DATEDIFF(date1, date2)

Parameters

`date1`	`Required.` Specify the first date or datetime value to calculate the difference (date1 - date2).
`date2`	`Required.` Specify the second date or datetime value to calculate the difference (date1 - date2).

Return Value

Returns the difference between two date/datetime values expressed in days.

Example 1:

The example below shows the usage of DATEDIFF() function.

mysql> SELECT DATEDIFF('2019-10-25', '2019-10-15');
Result: 10

mysql> SELECT DATEDIFF('2019-10-25 12:40:45', '2019-10-15 10:20:25');
Result: 10

mysql> SELECT DATEDIFF('2019-10-25 12:40:45.001234', '2019-10-15 10:20:25.001000');
Result: 10

mysql> SELECT DATEDIFF('2019-10-15', '2019-10-25');
Result: -10

mysql> SELECT DATEDIFF('2019-10-25 12:40:45', '2019-10-15');
Result: 10

Example 2:

Consider a database table called Orders with the following records:

OrderQuantity	Price	OrderDate	PaymentDate
100	1.58	2017-08-18	2017-08-20
120	1.61	2018-03-23	2018-03-28
125	1.78	2018-09-12	2018-09-22
50	1.80	2019-01-16	2019-01-17
200	1.72	2020-02-06	2020-02-08

To calculate the number of days taken to settle the deal, the following query can be used:

SELECT *, DATEDIFF(PaymentDate, OrderDate) AS SettlementDays
FROM Orders;

This will produce a result similar to:

OrderQuantity	Price	OrderDate	PaymentDate	SettlementDays
100	1.58	2017-08-18	2017-08-20	2
120	1.61	2018-03-23	2018-03-28	5
125	1.78	2018-09-12	2018-09-22	10
50	1.80	2019-01-16	2019-01-17	1
200	1.72	2020-02-06	2020-02-08	2

❮ MySQL Functions