How to Use DATEDIFF in MySQL: Syntax and Examples

In MySQL, use the DATEDIFF(date1, date2) function to get the number of days between two dates. It returns the difference as an integer, calculated as date1 - date2 in days.

Syntax

The DATEDIFF function takes two date arguments and returns the number of days between them. The syntax is:

• date1: The first date (usually the later date).
• date2: The second date (usually the earlier date).

The result is date1 - date2 in days.

sql

DATEDIFF(date1, date2)

Example

This example shows how to find the number of days between two dates using DATEDIFF. It calculates how many days passed from '2024-01-01' to '2024-01-10'.

sql

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

Output

days_difference
9

Common Pitfalls

Common mistakes when using DATEDIFF include:

• Swapping the order of dates, which changes the sign of the result.
• Using datetime values with time parts, but DATEDIFF only considers the date part.
• Expecting the function to return months or years difference; it only returns days.

sql

/* Wrong: date order swapped, result is negative */
SELECT DATEDIFF('2024-01-01', '2024-01-10') AS wrong_diff;

/* Right: correct date order for positive difference */
SELECT DATEDIFF('2024-01-10', '2024-01-01') AS correct_diff;

Output

wrong_diff
-9

correct_diff
9

Quick Reference

Function	Description	Returns
DATEDIFF(date1, date2)	Difference in days between date1 and date2	Integer (days)
Note	Result = date1 - date2	Positive or negative integer
Limitation	Only counts days, ignores time parts	N/A

Key Takeaways

Use DATEDIFF(date1, date2) to get the number of days between two dates in MySQL.

The result is date1 minus date2 in days and can be positive or negative.

DATEDIFF only considers the date part, ignoring time components.

Swapping the order of dates changes the sign of the result.

DATEDIFF returns days only, not months or years.