MariaDB Tutorial MariaDB Advanced MariaDB Database Account Management MariaDB References

MariaDB DATEDIFF() Function



The MariaDB 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.

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

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

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

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

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:

OrderQuantityPriceOrderDatePaymentDate
1001.582017-08-182017-08-20
1201.612018-03-232018-03-28
1251.782018-09-122018-09-22
501.802019-01-162019-01-17
2001.722020-02-062020-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:

OrderQuantityPriceOrderDatePaymentDateSettlementDays
1001.582017-08-182017-08-202
1201.612018-03-232018-03-285
1251.782018-09-122018-09-2210
501.802019-01-162019-01-171
2001.722020-02-062020-02-082

❮ MariaDB Functions

5