How to use the MySQL DATEDIFF() function

The DATEDIFF() function in MySQL is used to calculate the difference between two dates in terms of a specified unit like days, weeks, months etc.

Syntax

The syntax for DATEDIFF() is:

DATEDIFF(date1, date2, unit)

Where:

• date1 and date2 are the dates to calculate difference between
• unit is the unit of difference, like ‘DAY’, ‘WEEK’, ‘MONTH’ etc.

Examples

1. Get difference in days between two dates:

   SELECT DATEDIFF('2023-01-20', '2023-01-05', 'DAY');
   

   Returns a difference of 15 days.

2. Calculate weeks between two dates:

   SELECT DATEDIFF('2023-02-20', '2023-01-28', 'WEEK');
   

   Returns a difference of 3 weeks.

3. Get months difference between two dates:

   SELECT DATEDIFF('2023-05-05', '2023-02-15', 'MONTH');
   

   Returns a difference of 2 months.

4. Calculate years difference between two dates:

   SELECT DATEDIFF('2025-07-28', '2023-03-05', 'YEAR');
   

   Returns a difference of 2 years.

5. Get difference by day name:

   SELECT DATEDIFF('2023-01-15', '2023-01-08', 'DAYOFYEAR');
   

   Returns a difference of 7 days.

Other Similar Functions

• TIMEDIFF() - Time difference between two times
• TIMESTAMPDIFF() - Difference between timestamps
• TO_DAYS() - Convert date to days
• LAST_DAY() - Last day of month for a date

So DATEDIFF() provides a convenient way to calculate the difference between two dates in MySQL.