How to Calculate the Difference Between Two Dates in MySQL

Difference Between Two Dates, Date calculations are one of those SQL tasks that look simple until you need to answer a practical question:

How many days passed between two dates?

You might want to calculate employee tenure, order-to-delivery time, subscription duration, project length, or the number of days between two business events.

In MySQL, the DATEDIFF() function makes this calculation straightforward.

The basic syntax is:

DATEDIFF(end_date, start_date)

For example:

SELECT DATEDIFF('2024-05-19', '2024-02-13') AS days_between;

This returns:

96

But there is an important detail that can easily cause confusion: DATEDIFF() returns the number of day boundaries between two dates. It does not count both endpoints.

If you want an inclusive count—where both the starting and ending dates count as days—you can add 1:

DATEDIFF(end_date, start_date) +1

Let’s see exactly how this works.

What Does DATEDIFF() Do in MySQL?

The DATEDIFF() function calculates the difference between two dates.

Its syntax is:

DATEDIFF(date1, date2)

The result is approximately:

date1 - date2

So:

DATEDIFF(end_date, start_date)

means:

Calculate the number of days from start_date to end_date.

For example:

SELECT DATEDIFF('2024-02-10', '2024-02-09') AS date_diff;

Result:

+-----------+| date_diff |+-----------+|         1 |+-----------+

There is one day between February 9 and February 10.

Creating a Sample Dataset

Let’s use an employee example.

Suppose a company wants to analyze how long employees worked between their start and end dates.

Create a table:

CREATETABLE sales (  employee_ID INTPRIMARYKEY,  start_date DATENOTNULL,  end_date DATENOTNULL);

Insert some sample records:

INSERTINTO sales VALUES (1, '2024-02-09', '2024-02-10');INSERTINTO sales VALUES (2, '2024-10-19', '2024-11-25');INSERTINTO sales VALUES (3, '2024-07-22', '2024-07-30');INSERTINTO sales VALUES (4, '2024-01-04', '2024-01-14');INSERTINTO sales VALUES (5, '2024-02-13', '2024-05-19');

View the data:

SELECT*FROM sales;

The table contains:

+-------------+------------+------------+| employee_ID | start_date | end_date   |+-------------+------------+------------+|           1 | 2024-02-09 | 2024-02-10 ||           2 | 2024-10-19 | 2024-11-25 ||           3 | 2024-07-22 | 2024-07-30 ||           4 | 2024-01-04 | 2024-01-14 ||           5 | 2024-02-13 | 2024-05-19 |+-------------+------------+------------+

Now let’s calculate the number of days between the two dates.

Calculating the Date Difference

Use:

SELECT  employee_ID,  start_date,  end_date,  DATEDIFF(end_date, start_date) AS date_diffFROM sales;

The result is:

+-------------+------------+------------+-----------+| employee_ID | start_date | end_date   | date_diff |+-------------+------------+------------+-----------+|           1 | 2024-02-09 | 2024-02-10 |         1 ||           2 | 2024-10-19 | 2024-11-25 |        37 ||           3 | 2024-07-22 | 2024-07-30 |         8 ||           4 | 2024-01-04 | 2024-01-14 |        10 ||           5 | 2024-02-13 | 2024-05-19 |        96 |+-------------+------------+------------+-----------+

The date_diff column tells us how many days separate the two dates.

For employee 1:

2024-02-10 - 2024-02-09 = 1 day

For employee 5:

2024-05-19 - 2024-02-13 = 96 days

The Important Difference Between Exclusive and Inclusive Counting

This is where many date calculations become confusing.

Suppose an employee starts on:

February 9

and finishes on:

February 10

DATEDIFF() returns:

1

But if your question is:

“How many calendar days were they present, counting both February 9 and February 10?”

then the answer is:

2 days

That’s because you’re counting:

February 9 → Day 1February 10 → Day 2

To perform this inclusive calculation in MySQL:

DATEDIFF(end_date, start_date) +1

Calculating Both Versions

We can calculate both values at once:

SELECT  employee_ID,  start_date,  end_date,  DATEDIFF(end_date, start_date) AS date_diff,  DATEDIFF(end_date, start_date) +1AS date_diff_incFROM sales;

The result is:

+-------------+------------+------------+-----------+---------------+| employee_ID | start_date | end_date   | date_diff | date_diff_inc |+-------------+------------+------------+-----------+---------------+|           1 | 2024-02-09 | 2024-02-10 |         1 |             2 ||           2 | 2024-10-19 | 2024-11-25 |        37 |            38 ||           3 | 2024-07-22 | 2024-07-30 |         8 |             9 ||           4 | 2024-01-04 | 2024-01-14 |        10 |            11 ||           5 | 2024-02-13 | 2024-05-19 |        96 |            97 |+-------------+------------+------------+-----------+---------------+

The two columns answer slightly different questions.

date_diff

DATEDIFF(end_date, start_date)

Returns the number of days separating the two dates.

date_diff_inc

DATEDIFF(end_date, start_date) +1

Counts both the starting and ending dates.

Understanding this distinction is essential when working with durations.

Why Does Adding 1 Matter?

Consider a simple example:

Start: January 1End:   January 1

What should the duration be?

DATEDIFF() returns:

SELECT DATEDIFF('2024-01-01', '2024-01-01');

Result:

0

There are zero days between the two dates.

But if you’re counting how many calendar dates are included, January 1 itself is one day:

DATEDIFF('2024-01-01', '2024-01-01') +1

Result:

1

So the choice depends on what you’re trying to measure.

A Useful Mental Model

Think about the difference this way:

DATEDIFF() asks:

“How far apart are these two dates?”

DATEDIFF() + 1 asks:

“How many calendar dates are included from the first date through the second date?”

That small distinction can make a big difference in business calculations.

Reversing the Dates

The order of the arguments matters.

For example:

SELECT DATEDIFF('2024-05-19', '2024-02-13');

returns:

96

But reversing them:

SELECT DATEDIFF('2024-02-13', '2024-05-19');

returns:

-96

So remember:

DATEDIFF(later_date, earlier_date)

generally produces a positive result.

If you reverse the dates, you can get a negative value.

This can actually be useful when you want to determine whether one event occurred before or after another.

Using DATEDIFF() with a WHERE Clause

You can also use the function to filter records.

For example, suppose you want employees whose start and end dates are more than 30 days apart:

SELECT*FROM salesWHERE DATEDIFF(end_date, start_date) >30;

This lets MySQL calculate the duration for each row and return only records satisfying the condition.

You could also find records lasting at least 90 days:

SELECT*FROM salesWHERE DATEDIFF(end_date, start_date) >=90;

This is particularly useful when analyzing large datasets.

Finding Short-Duration Records

The same technique works in the opposite direction.

For example, find records lasting less than 10 days:

SELECT*FROM salesWHERE DATEDIFF(end_date, start_date) <10;

You can therefore use DATEDIFF() both for creating calculated columns and filtering data.

A Real-World Example: Order Processing Time

Imagine an e-commerce database containing:

order_datedelivery_date

You could calculate how many days each order took to arrive:

SELECT  order_id,  order_date,  delivery_date,  DATEDIFF(delivery_date, order_date) AS delivery_daysFROM orders;

This could help answer questions such as:

• Which orders took the longest to deliver?
• What is the average delivery time?
• How many orders arrived within seven days?
• Which orders exceeded the target delivery period?

The same SQL pattern can be applied to many types of time-based data.

Another Example: Subscription Duration

Suppose a subscription table contains:

customer_idsubscription_startsubscription_end

You can calculate the subscription duration:

SELECT  customer_id,  subscription_start,  subscription_end,  DATEDIFF(subscription_end, subscription_start) AS subscription_daysFROM subscriptions;

If the business considers both the start and end date part of the subscription period, use:

DATEDIFF(subscription_end, subscription_start) +1

The correct formula depends on the business definition of “duration.”

DATEDIFF() and Time Components

DATEDIFF() is focused on the date portion of the values.

For example, when working with date-time values, it calculates the difference in days rather than giving you a detailed difference in hours, minutes, and seconds.

If you need a more precise time difference, functions such as TIMESTAMPDIFF() may be more appropriate.

For example:

SELECT TIMESTAMPDIFF(HOUR, start_time, end_time);

So a useful rule is:

DATEDIFF()      → difference in daysTIMESTAMPDIFF() → difference in a specified time unit

Handling Dates in the Wrong Order

If your dataset can contain an end date earlier than a start date, DATEDIFF() will produce a negative number.

For example:

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

returns:

-9

That may indicate a data-quality problem.

You can use this behavior to identify potentially incorrect records:

SELECT*FROM salesWHERE end_date < start_date;

This is a useful validation technique when cleaning real-world datasets.

Calculating the Average Duration

Once you’ve calculated the date differences, you can also aggregate them.

For example:

SELECT AVG(DATEDIFF(end_date, start_date)) AS avg_durationFROM sales;

This returns the average number of days between the start and end dates.

You could also calculate the inclusive average:

SELECT AVG(DATEDIFF(end_date, start_date) +1) AS avg_duration_incFROM sales;

This is useful for analyzing average employee tenure, delivery time, project duration, or subscription length.

Quick Reference

Here are the most useful patterns:

-- Difference between two datesDATEDIFF(end_date, start_date)-- Inclusive differenceDATEDIFF(end_date, start_date) +1-- Find records lasting more than 30 daysWHERE DATEDIFF(end_date, start_date) >30-- Find records lasting exactly 30 daysWHERE DATEDIFF(end_date, start_date) =30-- Find invalid/reversed date rangesWHERE end_date < start_date

Final Takeaway

The MySQL DATEDIFF() function provides a simple way to calculate the number of days between two dates:

DATEDIFF(end_date, start_date)

If you need to count both the starting and ending dates, add one:

DATEDIFF(end_date, start_date) +1

For example:

SELECT  employee_ID,  start_date,  end_date,  DATEDIFF(end_date, start_date) AS date_diff,  DATEDIFF(end_date, start_date) +1AS date_diff_incFROM sales;

The most important thing to remember is that “days between” and “number of calendar days included” are not always the same thing.

Once you understand that distinction, DATEDIFF() becomes a powerful tool for analyzing employee durations, delivery times, subscriptions, project timelines, sales periods, and many other date-based datasets.