MySQL DATEDIFF() Function

Syntax

DATEDIFF(date1, date2)

DATEDIFF returns date1 - date2 in days. The result is an integer and can be negative.

Basic Example

SELECT DATEDIFF('2026-05-17', '2026-05-10') AS days_between;
-- Result: 7

Signed results (order matters)

SELECT DATEDIFF('2026-05-10', '2026-05-17') AS signed_days;
-- Result: -7

Practical query example

SELECT order_id, order_date
FROM orders
WHERE DATEDIFF(CURDATE(), order_date) > 30;

DATEDIFF vs TIMESTAMPDIFF

• Use DATEDIFF when you only need whole days.
• Use TIMESTAMPDIFF when you need hours, minutes, seconds, months, or years.

SELECT TIMESTAMPDIFF(HOUR, '2026-05-10 10:00:00', '2026-05-11 12:00:00') AS hours_between;
-- Result: 26

When to use it

Use DATEDIFF for retention windows, overdue checks, SLA aging buckets, and simple date-gap reporting in MySQL.

Common mistakes

• Reversing argument order and getting negative values unexpectedly.
• Expecting partial days from datetime values. DATEDIFF compares date parts only.
• Using the function name from another dialect without checking syntax differences.

Cross-dialect alternatives

• PostgreSQL: date subtraction, for example DATE '2026-05-17' - DATE '2026-05-10'
• SQL Server: DATEDIFF(day, start_date, end_date)
• Oracle: date subtraction, for example DATE '2026-05-17' - DATE '2026-05-10'

Troubleshoot MySQL DATEDIFF problems

Related SQL Functions

• MySQL CONCAT() Function
• MySQL CONCAT_WS() Function
• MySQL SUBSTRING() Function
• MySQL LEFT() Function
• MySQL RIGHT() Function
• MySQL REPLACE() Function

FAQ

Does MySQL DATEDIFF include time-of-day?

No. It compares the date portion only and returns whole days.

Why is my result negative?

The function computes date1 - date2. Swap arguments if you want a positive value.

Can I use DATEDIFF in WHERE clauses?

Yes, but for large tables you should test index-friendly alternatives if performance drops.