The DATEDIFF function returns the difference in the specified scale between two dates.
DATEDIFF(
start_date,
end_date,
interval
)
- start_date: Start date in datetime format.
- end_date: End date in datetime format.
- interval: Interval to use for the calculation. It can take any of the following values:
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
The DATEDIFF function returns an integer indicating the difference in the specified scale between two dates.
Importantly, the function returns the difference between the two dates (the number of difference intervals), not the number of intervals covered (which can be greater). Thus, the difference between December 31, 2015 and January 1, 2017 is not three years (even though the first date is 2015 and the last 2017), but two years (which is the difference between 2017 and 2015):
The interval argument is written without quotes.
The following example compares the date of December 31, 2015 at 23:59:59 to January 1, 2016 at 0:0:0 (one second later). As can be seen, the difference between both dates according to all the intervals (except for the week) is 1:
This example shows the number of weeks elapsed from the sale date to today (calculation involving the DATEDIFF and TODAY functions):
