Categories:

Date & Time Functions

DATEDIFF

Calculates the difference between two date, time, or timestamp expressions based on the date or time part requested. The function returns the result of subtracting the second argument from the third argument.

Alternatives:

TIMEDIFF , TIMESTAMPDIFF

Syntax

DATEDIFF( <date_or_time_part>, <date_or_time_expr1>, <date_or_time_expr2> )

Arguments

  • date_or_time_part must be one of the values listed in Supported Date and Time Parts.

  • date_or_time_expr1, date_or_time_expr2 must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The first value is subtracted from the second value.

Returns

Returns an integer representing the number of units (seconds, days, etc.) difference between date_or_time_expr1 and date_or_time_expr2.

Usage Notes

  • DATEDIFF supports years, quarters, months, weeks, days, hours, minutes, and seconds.

  • date_or_time_part must be one of the values listed in Supported Date and Time Parts.

    If date_or_time_part is week (or any of its variations), the output is controlled by the WEEK_START session parameter. For more details, including examples, see Calendar Weeks and Weekdays.

  • date_or_time_expr1 and date_or_time_expr2 can be a date, time, or timestamp.

  • When the unit used to calculate the difference is a date part (e.g. year, month, day) or time part (e.g. hour, minute, second), only the relevant part(s) are used.

    For example:

    • year uses only the year from the date and disregards all the other parts.

    • month uses the month and year from the date.

    • day uses the entire date.

    • hour uses only the hour from the time/timestamp and disregards all the other parts.

    • minute uses the hour and minutes from the time/timestamp.

    • second uses the entire time/timestamp.

    For details, see below.

Examples

Calculate the difference in years between two timestamps:

SELECT DATEDIFF(year, '2010-04-09 14:39:20'::TIMESTAMP, 
                      '2013-05-08 23:39:20'::TIMESTAMP) 
               AS diff_years;
+------------+
| DIFF_YEARS |
|------------|
|          3 |
+------------+

Calculate the difference in hours between two timestamps:

SELECT DATEDIFF(hour, '2013-05-08T23:39:20.123-07:00'::TIMESTAMP, 
    DATEADD(year, 2, ('2013-05-08T23:39:20.123-07:00')::TIMESTAMP)) 
               AS diff_hours;
+------------+
| DIFF_HOURS |
|------------|
|      17520 |
+------------+

Demonstrate how date parts affect DATEDIFF calculations:

SELECT column1 date_1, column2 date_2,
      DATEDIFF(year, column1, column2) diff_years,
      DATEDIFF(month, column1, column2) diff_months,
      DATEDIFF(day, column1, column2) diff_days,
      column2::DATE - column1::DATE AS diff_days_via_minus
    FROM VALUES
      ('2015-12-30', '2015-12-31'),
      ('2015-12-31', '2016-01-01'),
      ('2016-01-01', '2017-12-31'),
      ('2016-08-23', '2016-09-07');
+------------+------------+------------+-------------+-----------+---------------------+
| DATE_1     | DATE_2     | DIFF_YEARS | DIFF_MONTHS | DIFF_DAYS | DIFF_DAYS_VIA_MINUS |
|------------+------------+------------+-------------+-----------+---------------------|
| 2015-12-30 | 2015-12-31 |          0 |           0 |         1 |                   1 |
| 2015-12-31 | 2016-01-01 |          1 |           1 |         1 |                   1 |
| 2016-01-01 | 2017-12-31 |          1 |          23 |       730 |                 730 |
| 2016-08-23 | 2016-09-07 |          0 |           1 |        15 |                  15 |
+------------+------------+------------+-------------+-----------+---------------------+

Demonstrate how time parts affect DATEDIFF calculations:

ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'DY, DD MON YYYY HH24:MI:SS';
SELECT column1 timestamp_1, column2 timestamp_2,
      DATEDIFF(hour, column1, column2) diff_hours,
      DATEDIFF(minute, column1, column2) diff_minutes,
      DATEDIFF(second, column1, column2) diff_seconds
    FROM VALUES
      ('2016-01-01 01:59:59'::TIMESTAMP, '2016-01-01 02:00:00'::TIMESTAMP),
      ('2016-01-01 01:00:00'::TIMESTAMP, '2016-01-01 01:59:00'::TIMESTAMP),
      ('2016-01-01 01:00:59'::TIMESTAMP, '2016-01-01 02:00:00'::TIMESTAMP);
+---------------------------+---------------------------+------------+--------------+--------------+
| TIMESTAMP_1               | TIMESTAMP_2               | DIFF_HOURS | DIFF_MINUTES | DIFF_SECONDS |
|---------------------------+---------------------------+------------+--------------+--------------|
| Fri, 01 Jan 2016 01:59:59 | Fri, 01 Jan 2016 02:00:00 |          1 |            1 |            1 |
| Fri, 01 Jan 2016 01:00:00 | Fri, 01 Jan 2016 01:59:00 |          0 |           59 |         3540 |
| Fri, 01 Jan 2016 01:00:59 | Fri, 01 Jan 2016 02:00:00 |          1 |           60 |         3541 |
+---------------------------+---------------------------+------------+--------------+--------------+