How to Use MySQL DATEDIFF() to Compare Dates

Key Takeaways

1. DATEDIFF() calculates the number of days between two dates in MySQL.
2. A positive or negative result reflects the order of the two dates.
3. Common use cases include filtering recent records and calculating date-based metrics.

In MySQL, working with dates is a common task, and calculating the difference between two dates is particularly useful in reporting, data analysis, and filtering records based on time. The DATEDIFF() function is one of the most straightforward and frequently used functions for this purpose.

This article explains what DATEDIFF() is, how it works, and provides practical examples of how to use it in SQL queries.

---

What Is DATEDIFF()?

The DATEDIFF() function in MySQL returns the number of days between two dates. It subtracts the second date (the end date) from the first date (the start date), returning the result as an integer.

Syntax:

DATEDIFF(date1, date2)

• date1: The first date (start date)
• date2: The second date (end date)

Note: The result is date1 - date2. If date1 is earlier than date2, the result will be negative.

---

Examples of Using DATEDIFF()

1. Basic Example

SELECT DATEDIFF('2025-04-25', '2025-04-20') AS days_difference;

Result:

days_difference
---------------
5

This shows that there are 5 days between April 25, 2025 and April 20, 2025.

2. Negative Result

SELECT DATEDIFF('2025-04-20', '2025-04-25') AS days_difference;

Result:

days_difference
---------------
-5

When the first date is earlier, the result is negative.

3. Using DATEDIFF() in a WHERE Clause

Suppose you want to find all orders made within the last 30 days.

SELECT *
FROM orders
WHERE DATEDIFF(CURDATE(), order_date) <= 30;

This query retrieves orders where the difference between the current date and the order_date is 30 days or less.

---

Use Cases

Here are some common scenarios where DATEDIFF() is useful:

• Finding overdue tasks or payments
• Calculating the age of records
• Filtering data based on time windows (e.g., last 7 days, last 90 days)
• Measuring durations between two event timestamps

---

Considerations

• DATEDIFF() only returns the number of full days between dates. It does not consider hours, minutes, or seconds.
• It works with both DATE and DATETIME values, but the time portion is ignored.

---

Conclusion

The DATEDIFF() function is a simple but powerful tool for comparing dates in MySQL. Whether you're generating reports, monitoring time-based KPIs, or analyzing user activity, DATEDIFF() can help you extract meaningful insights from your date fields.

FAQs

Does DATEDIFF() consider time (hours/minutes/seconds)?

No, DATEDIFF() only calculates the difference in full days.

Can I use DATEDIFF() with DATETIME columns?

Yes, but only the date portion is used; the time part is ignored.

What happens if the first date is earlier than the second date?

The result will be a negative number, indicating the number of days before.

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ