The MySQL DATEDIFF() function allows you to count the number of days between two different date expressions.
The date expressions can be of DATE, DATETIME, or TIMESTAMP type. You can put literal date expressions or column names of date type.
You need to pass the date expressions as arguments to the DATEDIFF() function:
Here’s the syntax for the DATEDIFF() function:
SELECT DATEDIFF(date_expression, date_expression);
The differences are calculated by subtracting the second argument from the first argument.
Take a look at the following example:
SELECT DATEDIFF("2021-10-20", "2021-10-10");
-- 2021-10-20 - 2021-10-10 = 10
When your first argument is an earlier date than the second argument, then the result will be a negative number:
SELECT DATEDIFF("2021-10-10", "2021-10-20");
-- +--------------------------------------+
-- | DATEDIFF("2021-10-10", "2021-10-20") |
-- +--------------------------------------+
-- | -10 |
-- +--------------------------------------+
You can put the time section in your arguments, but it won’t have any effect on the result:
SELECT DATEDIFF("2021-10-20 15:25:35", "2021-10-10 05:25:35");
-- +--------------------------------------------------------+
-- | DATEDIFF("2021-10-20 15:25:35", "2021-10-10 05:25:35") |
-- +--------------------------------------------------------+
-- | 10 |
-- +--------------------------------------------------------+
You can also mix the arguments between one date type and another.
For example, DATE and DATETIME can still be compared like this:
SELECT DATEDIFF("2021-10-20", "2021-10-10 05:25:35");
-- +-----------------------------------------------+
-- | DATEDIFF("2021-10-20", "2021-10-10 05:25:35") |
-- +-----------------------------------------------+
-- | 10 |
-- +-----------------------------------------------+
This means that you can use two date columns with different types as arguments to the function.
In the example table below, the join_date is of DATE type while :
+------------+---------------------+
| join_date | last_update |
+------------+---------------------+
| 2021-09-22 | 2021-09-25 09:20:10 |
| 2021-09-23 | 2021-09-29 09:20:10 |
+------------+---------------------+
You can compare the table columns above to find how many days passed between last_update and join_date as shown below:
SELECT DATEDIFF(last_update, join_date) FROM example;
-- +----------------------------------+
-- | DATEDIFF(last_update, join_date) |
-- +----------------------------------+
-- | 3 |
-- | 6 |
-- +----------------------------------+
That’s pretty neat, right?
Finally, you can also show how many weeks or months have passed between two dates by dividing the result by 7 or 30:
SELECT DATEDIFF("2021-10-13", "2021-07-25") / 7 AS weeks_passed;
-- +--------------+
-- | weeks_passed |
-- +--------------+
-- | 11.4286 |
-- +--------------+
SELECT DATEDIFF("2021-10-13", "2021-07-25") / 30 AS months_passed;
-- +---------------+
-- | months_passed |
-- +---------------+
-- | 2.6667 |
-- +---------------+
You can round the number down using the FLOOR() function on the result numbers if you don’t want MySQL to return floating numbers:
SELECT FLOOR(DATEDIFF("2021-10-13", "2021-07-25") / 30) AS months_passed;
-- +---------------+
-- | months_passed |
-- +---------------+
-- | 2 |
-- +---------------+
You’ve just learned how to use the DATEDIFF() function to count the number of days between two different dates in MySQL.
Nice work! 👍