Hit enter after type your search item
Home / SQL Tutorials / What is MySQL DATEDIFF function?

What is MySQL DATEDIFF function?

The MySQL DATEDIFF function returns the number of days between two given dates. The DATEDIFF takes two arguments as shown in the example below:


Both arguments are dates that you want to get the difference in terms of the number of days.

In the above example, the CURDATE() function took the current date from the system where MySQL database is installed.

The first should be recent than the second date, otherwise, DATEDIFF returns a negative number. I will show you in the example below.

The example of using static date value in DATEDIFF function

In the first simple example, I used a static date for getting the difference of days. The one is the current date by using the CURDATE() function while the other is given 2018-02-16. See the result as I executed this query:

The DATEDIFF query:


The result (assuming the current date is 6th May 2018):

MySQL DATEDIFF

What if CURDATE() was used as the second argument?

Now see the outcome as I used CURDATE() function as the second argument in DATEDIFF MySQL function. The other date is the same as in above example; just the order is changed:


The result:

MySQL DATEDIFF curdate

The result is -79. So, you should use the higher date as the first argument to get the difference as the positive number. In the case of a future date in above example, the DATEDIFF() should produce the positive result. For example:

DATEDIFF(“2019-02-16”, CURDATE())

Using the table column data with DATEDIFF function

For this example, we will use the table data for calculating the difference in number of days. For that, the tbl_products table is used that stores product information including last modified date.

In the query, the CURDATE() function is used with DATEDIFF() function and the second argument is the date_modified column. The query returns the difference in days for complete table data (dummy)

The query with DATEDIFF():


The result-set:

MySQL DATEDIFF table

How to get months difference in MySQL?

Well, until now we have seen getting the difference in terms of days between the two given dates while using the DATEDIFF function. What about getting the difference in months?

One way can be getting the days difference and dividing by 28, 30, 31, however, this sounds tricky and hard.

The easy way of getting the months difference is using the TIMESTAMPDIFF() function of MySQL.

The TIMESTAMPDIFF() function allows you specifying which unit difference you require. For example, Month, Year, Day, Minute, Hour etc.

A sample query for getting the Months difference is:

The example of getting Month, Year, Days, Hours and Minutes difference

By using the same table as in above example, we will get the difference between two dates in terms of:

  • Years
  • Months
  • Days
  • Hours
  • Minutes

by using the TIMESTAMPDIFF() function as follows:


Assume the current date is 6th  of May 2018. You may compare the date in the “date_modified” column and differences in respective columns. The result-set:

MySQL TIMESTAMPDIFF

Is not that pretty simple!

This div height required for enabling the sticky sidebar