How to use MySQL DATEDIFF

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

What is MySQL DATEDIFF?

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.

MySQL DATEDIFF syntax

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

SELECT DATEDIFF ("Expression1", "Expression2")
bash

‘Expression1’ represents the first date and ‘Expression2’ the second.

MySQL DATEDIFF examples

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

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

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

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

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");
bash

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");
bash

Here, the output will be:

DATEDIFF ("2022-01-01", "2022-01-30")
-29
bash
Was this article helpful?