MySQL DATEDIFF() function

In this context, we will learn how we can use the MySQL DATEDIFF() function with proper syntax and good examples.

Introduction of MySQL DATEDIFF() function

DATEDIFF() function in MySQL is used to return the number of days between two mentioned date values, DATETIME or TIMESTAMP values.

Syntax of the MySQL DATEDIFF() function

The syntax of the MySQL DATEDIFF() function is given as follows:

DATEDIFF(dt1, dt2)

Parameters or arguments used in MySQL DATEDIFF() function:

There are two parameters accepted by the DATEDIFF() function in MySQL, which are given as follows:

If we pass DATETIME or TIMESTAMP values, the DATEDIFF function only takes the date parts for calculation and ignores the time parts.

dt1: This is the first mentioned date.

dt2: This is the second mentioned date.

Returns:

It will return the number of days between the two mentioned date values.

Application used for DATEDIFF() function:

The DATEDIFF() function can be used in the given below MySQL versions.:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

The DATEDIFF function is useful in many cases, e.g., we can calculate an interval in days that the products need to ship to a customer.

Examples of MySQL DATEDIFF() function:

Now we will look into some MySQL DATEDIFF() function examples and will explore how we can use the DATEDIFF function in MySQL.

Example 1 :

In this example, we will get the number of days between two mentioned date values where the date is mentioned in the format of YYYY-MM-DD. Here, date1 is greater than date2, so the return value is positive.

SELECT DATEDIFF("2020-11-20", "2020-11-1");

Output:

19
1 row in set (0.00 sec)

Example 2:

In this example, we will get the number of days between two mentioned date values where the date is mentioned in the format of YYYY-MM-DD. Here the date1 is less than date2, so the return value is negative.

SELECT DATEDIFF("2020-11-12", "2020-11-19");

Output:

-7
1 row in set (0.00 sec)

Example 3:

In this example, we will get the number of days between two mentioned date values where the date is mentioned in the format of YYYY-MM-DD HH-MM-SS.

SELECT DATEDIFF("2020-11-20 09:34:21", "2020-11-17 09:34:21");

Output:

3
1 row in set (0.00 sec)

Example 4:

In this example, we will get the number of days between two mentioned date values where the date is mentioned in the format of YYYY-MM-DD HH-MM-SS. Here time value does not matter as date1 and date2 are taken the same but time is different; still, the output is zero (0).

SELECT DATEDIFF("2020-11-20 09:34:21", "2020-11-20 08:11:23");

Output:

0
1 row in set (0.00 sec)

Some more examples of MySQL DATDIFF function:

Example 1:

SELECT DATEDIFF('2011-08-17','2011-08-17');

Output:

0 day
1 row in set (0.00 sec)

Example 2:

SELECT DATEDIFF('2011-08-17','2011-08-08');

Output:

9 days
1 row in set (0.00 sec)

Example 3:

SELECT DATEDIFF('2011-08-08','2011-08-17');

Output:

9 days
1 row in set (0.00 sec)

Example:

Now let's visualize the following sales table in the sample database.

Sales

Salenumber

Saledate

Shippeddate

Status

Comments

consumernumber

Table- Sales

To calculate the number of days between the required date and the shipped date of the sales, we use the DATEDIFF function as follows:

SELECT
orderNumber,
DATEDIFF(requiredDate, shippedDate) AS daysLeft
FROM
sales
ORDER BY daysLeft DESC;

Output:

Salenumber	Daysleft
20167	18
20180	15
20277	13
20179	10
20115	10
20017	10
20175	10

The following statement gets all sales whose statuses are in-process and calculates the number of days between the ordered date and the required date:

SELECT
orderNumber,
DATEDIFF(requiredDate, orderDate) remaining_days
FROM
sales
WHERE
status = 'In Process
ORDER BY remaining_days;

For calculating an interval in week or month, we can divide the returned value of the DATEDIFF function by 7 or 30 as the following query:

SELECT
orderNumber,
ROUND(DATEDIFF(requiredDate, orderDate) / 7, 2),
ROUND(DATEDIFF(requiredDate, orderDate) / 30,2)
FROM
sales
WHERE
status = 'In Process';

The important point to be noted is that the ROUND function is used to round the results.

Application of MySQL DATEDIFF() function:

This function is used to return the number of days between the two mentioned date values.

Summary:

In the above context, we have learned how to use the DATEDIFF() function in MySQL to return the number of days between the two mentioned date values.