DATEDIFF

The DATEDIFF function returns the difference in the specified scale between two dates.

Syntax

DATEDIFF(
    start_date,
    end_date,
    interval
)

Parameters
  • 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
Returned value

The DATEDIFF function returns an integer indicating the difference in the specified scale between two dates.

Additional Information

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):

DATEDIFF function. Example of use

The interval argument is written without quotes.

Examples

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:

DATEDIFF function. Example of use

DATEDIFF function. Example of use

DATEDIFF function. Example of use

DATEDIFF function. Example of use

DATEDIFF function. Example of use

DATEDIFF function. Example of use

DATEDIFF function. Example of use

DATEDIFF function. Example of use

This example shows the number of weeks elapsed from the sale date to today (calculation involving the DATEDIFF and TODAY functions):

DATEDIFF function. Example of use

Category
Date and time
Submitted by admin on Mon, 12/03/2018 - 23:26