How to Use the DATEDIFF() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the DATEDIFF() function in MySQL.

Using the DATEDIFF() function in MySQL

The DATEDIFF() function in MySQL is used to calculate the number of days between two dates. It returns the difference between two dates as a signed integer value, where negative values indicate that the second date is earlier than the first date, and positive values indicate that the second date is later than the first date.

Syntax

DATEDIFF(end_date, start_date);

Examples

Here are some examples of using the DATEDIFF() function in MySQL:

Calculating the difference between two dates

To calculate the number of days between two dates, you can use the DATEDIFF() function.

Syntax
SELECT DATEDIFF(end_date, start_date);
Example
SELECT DATEDIFF('2022-05-15', '2022-05-10');

Output:

+--------------------------------+
| DATEDIFF('2022-05-15', '2022-05-10') |
+--------------------------------+
| 5                              |
+--------------------------------+

In this example, the DATEDIFF() function calculates the number of days between the start date ‘2022-05-10’ and the end date ‘2022-05-15’.

Calculating the number of days between a date and today

To calculate the number of days between a date and today’s date, you can use the DATEDIFF() function with the CURDATE() function.

Syntax
SELECT DATEDIFF(CURDATE(), date);
Example
SELECT DATEDIFF(CURDATE(), '2022-05-15');

Output:

+--------------------------------+
| DATEDIFF(CURDATE(), '2022-05-15')   |
+--------------------------------+
| -680                            |
+--------------------------------+

In this example, the DATEDIFF() function calculates the number of days between the date ‘2022-05-15’ and today’s date.

Calculating the number of days between two dateTime values

You can also use the DATEDIFF() function to calculate the number of days between two datetime values.

Syntax
SELECT DATEDIFF(end_datetime, start_datetime);
Example
SELECT DATEDIFF('2022-05-15 08:30:00', '2022-05-10 12:00:00');

Output:

+------------------------------------+
| DATEDIFF('2022-05-15 08:30:00', '2022-05-10 12:00:00') |
+------------------------------------+
| 4                                  |
+------------------------------------+

In this example, the DATEDIFF() function calculates the number of days between the start datetime ‘2022-05-10 12:00:00’ and the end datetime ‘2022-05-15 08:30:00’.

#MySQL