The DATEDIFF() function in MySQL calculates the number of days between two date values. It subtracts the second date (date2) from the first (date1) and returns the difference in terms of the number of days.
Definition and Usage
- The
DATEDIFF()function returns the number of days between two dates, calculated asdate1 - date2.
Syntax
DATEDIFF(date1, date2)
Parameter Values
- date1: The first date, from which the difference is calculated.
- date2: The second date, which is subtracted from the first date.
Technical Details
- Works in: From MySQL 4.0
- Return Value: The difference in days between
date1anddate2. The result can be negative ifdate2is later thandate1.
Examples
Example 1: Calculate the difference between two dates
SELECT DATEDIFF("2017-06-25", "2017-06-15");
Output:
10
This query returns the number of days between 2017-06-25 and 2017-06-15, which is 10 days.
Example 2: Calculate the difference between two datetime values
SELECT DATEDIFF("2017-06-25 09:34:21", "2017-06-15 15:25:35");
Output:
10
This query also returns 10, as the time part is ignored and only the date part is considered.
Example 3: Calculate the difference between a date in the previous year and the current year
SELECT DATEDIFF("2017-01-01", "2016-12-24");
Output:
7
This query returns 7, as the difference between 2017-01-01 and 2016-12-24 is 7 days.
Related Functions
TIMESTAMPDIFF(): Returns the difference between two date/datetime values in a specified unit (e.g., years, months, days, hours).DATE_ADD(): Adds a time/date interval to a date.DATE_SUB(): Subtracts a time/date interval from a date.
Use Cases
- Age Calculation: Used to calculate the number of days between a birthdate and the current date, for example, to calculate someone's age in days.
- Event Time Difference: Useful for calculating the number of days between two events or dates (e.g., project deadlines, delivery dates).
- Date Comparisons: Helpful for comparing dates to find durations or gaps between important events.