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.

Table of Contents

## Syntax of MySQL DATEDIFF()

```
DATEDIFF(date1, date2)
```

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';
```

And the output is –

### 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';
```

And the output is –

### 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';
```

And the output is –

### 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';
```

And the output is –

### 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';
```

And the output is –

### 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';
```

And the output is –

### Working With Tables

Finally, let us see an example of `DATEDIFF()`

with tables. Consider the below 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;
```

And the output is –

## 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

- MySQL Official Documentation on the
`DATEDIFF()`

function.