MySQL DATEDIFF() function

DATEDIFF() function

MySQL DATEDIFF() returns the number of days between two dates or datetimes. This function only calculates the date portion from each expression. It's a useful function for performing date-based calculations and obtaining insights into the duration between two points in time.

This function is useful in -

• When analyzing historical data, DATEDIFF() helps quantify the time gap between significant events or milestones.
• In human resources, DATEDIFF() can be used to calculate the tenure of employees by determining the number of days between their hiring date and the current date.
• DATEDIFF() aids in tracking the duration it takes to complete tasks by calculating the number of days between task start and completion dates.
• For interest calculations or financial forecasting, DATEDIFF() can be used to determine the number of days between payment dates or compounding intervals.
• In performance analysis, DATEDIFF() helps measure the time elapsed between different phases of a process, enabling performance evaluation and optimization.

Syntax:

DATEDIFF(expr1,expr2);

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL DATEDIFF() function

The following statement will return the days between two datetime expressions 2008-05-17 11:31:31 and 2008-04-28.

Code:

SELECT DATEDIFF('2008-05-17 11:31:31','2008-04-28');

Output:

mysql> SELECT DATEDIFF('2008-05-17 11:31:31','2008-04-28');
+----------------------------------------------+
| DATEDIFF('2008-05-17 11:31:31','2008-04-28') |
+----------------------------------------------+
|                                           19 | 
+----------------------------------------------+
1 row in set (0.00 sec)

Example : DATEDIFF() function using table

The following statement will return those rows from the table purchase in which the difference of days between ‘invoice_dt’ and ‘ord_date’ are less than 10.

Sample table: purchase

Code:

SELECT invoice_dt,ord_date,DATEDIFF(invoice_dt,ord_date)
FROM purchase           
WHERE DATEDIFF(invoice_dt,ord_date)<10;  

Output:

mysql> SELECT invoice_dt,ord_date,DATEDIFF(invoice_dt,ord_date)
    -> FROM purchase           
    -> WHERE DATEDIFF(invoice_dt,ord_date)< 10; 
+------------+------------+-------------------------------+
| invoice_dt | ord_date   | DATEDIFF(invoice_dt,ord_date) |
+------------+------------+-------------------------------+
| 2008-07-15 | 2008-07-06 |                             9 | 
| 2008-09-20 | 2008-09-15 |                             5 | 
| 2007-08-30 | 2007-08-22 |                             8 | 
| 2007-09-24 | 2007-09-20 |                             4 | 
+------------+------------+-------------------------------+
4 rows in set (0.00 sec)

Video Presentation:>

All Date and Time Functions :

Click here to see the MySQL Date and time functions.

Previous: DATE()
Next: DAY()