How can I use MySQL to calculate the difference between two dates?

---

You can use the following syntax to calculate the difference between two dates in MySQL:

SELECT
  DATEDIFF(end_date, start_date) AS date_diff,
  DATEDIFF(end_date, start_date) + 1 AS date_diff_inc
FROM sales;

This particular example creates the following two columns:

• date_diff: Number of days between start_date and end_date columns.
• date_diff_inc: Number of days between start_date and end_date columns, inclusive.

The following example shows how to use this syntax in practice.

Example: How to Calculate Difference Between Two Dates in MySQL

Suppose we have the following table named sales that contains information about when various employees started and stopped working at some company:

-- create table 
CREATE TABLE sales (
  employee_ID INT PRIMARY KEY,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL
);

-- insert rows into table
INSERT INTO sales VALUES (0001, '2024-02-09', '2024-02-10');
INSERT INTO sales VALUES (0002, '2024-10-19', '2024-11-25');
INSERT INTO sales VALUES (0003, '2024-07-22', '2024-07-30');
INSERT INTO sales VALUES (0004, '2024-01-04', '2024-01-14');
INSERT INTO sales VALUES (0005, '2024-02-13', '2024-05-19');

-- view all rows in table
SELECT * FROM sales;

Output:

+-------------+------------+------------+
| 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 |
+-------------+------------+------------+

Suppose that we would like to calculate the difference between corresponding dates in the start_date and end_date columns.

We can use the following syntax to do so:

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

Output:

+-------------+------------+------------+-----------+---------------+
| 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 |
+-------------+------------+------------+-----------+---------------+

Notice that the new columns named date_diff and date_diff_inc display the date difference between the corresponding dates in the start_date and end_date columns.

Also notice the subtle difference between these two columns: The date_diff_inc column displays the date difference including both the start and end date.

The date_diff_inc column displays a date difference of 2 days for this employee because it counts both the start and end date.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Select Rows where Date is Equal to Today