MySQL DATEDIFF() – Find the Difference Between the Dates in MySQL

By /
DATEDIFF Function

In this tutorial, we will study the MySQL DATEDIFF() function. Suppose you are an HR executive at a company and you have data on the check-in date and time for each employee for today as well as the date they first joined the company in a table. The CEO of the company has tasked you with finding out how many days have elapsed since each employee joined the company.

Now doing this manually would be a mammoth task. Some employees may have joined more than 5 years ago or so and besides, your CEO wants the exact number of days each employee has been in the company. But we have a computer and we know MySQL, so let us make things easy for ourselves. Let us see how we can use the MySQL DATEDIFF() function to solve this problem.

The MySQL DATEDIFF() function is used to find the difference between two dates or datetime values. In other words, it returns the number of days between two dates. Let us dive deep and take a look at the syntax before we move on to the examples.

Syntax of MySQL DATEDIFF()

DATEDIFF(date1, date2)Code language: SQL (Structured Query Language) (sql)

Where, ‘date1’ and ‘date2’ are two date or datetime expressions.

Note that DATEDIFF() calculates the differences by subtracting date2 from date1, i.e. MySQL DATEDIFF() computes and returns the value of date1– date2.

Examples of MySQL DATEDIFF()

Let us start by looking at a few basic examples. Let us find the number of days between the following –

  • ‘2020-05-31’ and ‘2020-05-01’ ,and
  • ‘2020-06-15’, ‘2020-05-01’.

We will use the SELECT statement and an alias called ‘Number of Days’ to make our output readable. The query is –

SELECT DATEDIFF('2020-05-31', '2020-05-01') AS 'Number of Days'; 
SELECT DATEDIFF('2020-06-15', '2020-05-01') AS 'Number of Days'; Code language: SQL (Structured Query Language) (sql)

And the output is –

Datediff Basic Example

MySQL DATEDIFF() With Larger Differences

DATEDIFF() can support large differences between the date values, unlike the TIMEDIFF() function. However, make sure you enter valid date values. Let us see a couple of examples where the dates are years apart. Consider the below queries.

SELECT DATEDIFF('2021-03-01', '2000-02-25') AS 'Number of Days'; 
SELECT DATEDIFF('2021-01-01', '1999-11-19') AS 'Number of Days';Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Datediff Large Values

DATEDIFF() With Wrong Date Values

Building on what I said earlier about having valid date values, what if we pass invalid date values? If either of the date in the DATEDIFF() function is wrong or invalid, then the DATEDIFF() function returns NULL. Let us see an example of this.

SELECT DATEDIFF('2020-56-12', '2019-05-15') AS 'Number of Days';Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL Datediff Wrong Dates

MySQL DATEDIFF() With Negative Days Difference

If, by mistake, you pass the second date value as a value larger than the first, you’ll get a negative value for the date difference. This is alright as DATEDIFF() supports a negative date difference. Let us see an example of this. Consider the below query.

SELECT DATEDIFF('2000-02-25', '2021-03-01') AS 'Number of Days';Code language: SQL (Structured Query Language) (sql)

And the output is –

Datediff Negative

DATEDIFF() With CURDATE()

We can also use the CURDATE() function with the DATEDIFF() function. Let us find out the number of days between the present day (At the time this article was written, the date was 17th March, 2021) and the date India got independence on August 15, 1947. The query is –

SELECT DATEDIFF(CURDATE(), '1947-08-15') AS 'Number of Days';Code language: SQL (Structured Query Language) (sql)

And the output is –

Datediff Curdate

DATEDIFF() With Datetime Values

As I mentioned in the syntax section, DATEDIFF() also works with datetime values. However, while computation of the difference between the values, the time value in the datetime value is ignored. Let us see an example of DATEDIFF() with datetime values.

SELECT DATEDIFF('2020-05-31 00:00:30', '2020-05-01 23:59:59') AS 'Number of Days';Code language: SQL (Structured Query Language) (sql)

And the output is –

Datediff Datetime

Working With Tables

Finally, let us see an example of DATEDIFF() with tables. Consider the below Employee table.

Timediff Employee Table
Employee Table

Now, coming back to the problem I mentioned in the beginning of this article. Suppose you are an HR executive at a company and you have data on the check-in date and time for each employee for today as well as the date they first joined the company in a table. The CEO of the company has tasked you with finding out how many days have elapsed since each employee joined the company.

Now that we know how to use the DATEDIFF() function, let us use it to solve the above problem. Our query is –

SELECT eid, Name, DATEDIFF(CheckInDateTime, Date_Joined) AS 'Number Of Days' FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

Datediff Table Example

Conclusion

DATEDIFF() is a very important datetime function. Basic subtraction operations are always very important and have abundant use cases. I would encourage you to practice more examples of this function!

References