MySQL DATEDIFF Function

MySQL DATEDIFF() Function

The DATEDIFF() function in MySQL calculates the difference in days between two date values. It returns the number of days from the first date to the second date.

Syntax

DATEDIFF(end_date, start_date)

Parameters:

end_date: The later date.
start_date: The earlier date.

Return Value:

Returns an integer representing the number of days between the two dates.
A positive value if end_date is after start_date.
A negative value if end_date is before start_date.
Returns NULL if either date is NULL.

Examples

1. Basic Usage

SELECT DATEDIFF('2025-12-31', '2025-12-01') AS days_difference;

Output:

+-----------------+
| days_difference |
+-----------------+
| 30              |
+-----------------+

2. When the Start Date is After the End Date

SELECT DATEDIFF('2025-01-01', '2025-01-10') AS days_difference;

Output:

+-----------------+
| days_difference |
+-----------------+
| -9              |
+-----------------+

Here, the result is negative because the start date is after the end date.

3. Using DATEDIFF on Table Data

Assume we have an orders table:

SELECT order_id, order_date, delivery_date, 
       DATEDIFF(delivery_date, order_date) AS days_to_delivery
FROM orders;

This query calculates the number of days between order_date and delivery_date.

4. Using DATEDIFF in a WHERE Clause

Find all orders that took more than 7 days to deliver:

SELECT * FROM orders WHERE DATEDIFF(delivery_date, order_date) > 7;

Handling NULL and Invalid Dates

SELECT DATEDIFF(NULL, '2025-12-01');  -- Output: NULL
SELECT DATEDIFF('2025-12-31', NULL);  -- Output: NULL

Key Notes:

Only works with DATE, DATETIME, or TIMESTAMP values.
Ignores time parts; only counts full days.
If you need precise differences including time, use TIMESTAMPDIFF() instead.

Alternative: Getting Difference in Other Units

If you need differences in hours, minutes, or seconds, use:

SELECT TIMESTAMPDIFF(HOUR, '2025-12-31 10:00:00', '2026-01-01 12:00:00');

Output: 26 (26 hours difference).

Conclusion

DATEDIFF() calculates the number of days between two dates.
Ignores time components and returns an integer.
Useful for tracking date differences in orders, projects, and event logs.

A handy function for date-based calculations in MySQL! 🚀

Souy Soeng

MySQL DATEDIFF Function