How to calculate date differences with MySQL DATEDIFF

With MySQL DATEDIFF you can display the number of days between two dates. The values are either positive or negative.


To work with MySQL even more purposefully, it’s worth knowing some data and time functions. Besides MySQL DATE, of the database management systems most useful commands is MySQL DATEDIFF. It outputs the number of days between two dates. For this, these must only be stored as permitted data values. The output is then either a positive or a negative value, depending on whether the period is queried chronologically or not.


The syntax of MySQL DATEDIFF is very simple. If you want to learn MySQL, just remember the following command.

SELECT DATEDIFF ("Expression1", "Expression2")

“Expression1” represents the first date and “Expression2” the second.


Using a simple example, you can quickly see how MySQL DATEDIFF works:

SELECT DATEDIFF ("2022-01-27", "2022-01-17");

If you execute this command, you’ll get the following output:

DATEDIFF ("2022-01-27", "2022-01-17")

MySQL DATEDIFF and longer time intervals

Even with longer time specifications, MySQL DATEDIFF only filters relevant information. So, if you specify a date and a time, MySQL DATEDIFF will only consider the two dates. This looks like this:

SELECT DATEDIFF ("2022-01-27 14:22:43", "2022-01-17 20:19:35");

In this example, the output would be “10”.

An example of a negative value

You can also use MySQL DATEDIFF if the earlier date is queried first followed by the later date. In this case, you will get a negative value. This looks like this:

SELECT DATEDIFF ("2022-01-01", "2022-01-30");

Here, the output will be:

DATEDIFF ("2022-01-01", "2022-01-30")