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.

The minus sign (“-“) can also be used to subtract dates.

See also:: TIMEDIFF , TIMESTAMPDIFF

Syntax¶

Syntax for DATEDIFF

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

Syntax for minus sign

<date_or_time_expr2> - <date_or_time_expr1>

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 value date_or_time_expr1 is subtracted from date_or_time_expr2.

Returns¶

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

For date subtraction with the minus sign, the units are always days.

Usage Notes¶

Values can be negative, for example, -12 days.

For DATEDIFF

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.

For minus sign

date_or_time_expr1 and date_or_time_expr2 must both be dates.

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; also, demonstrate use of the minus sign for date subtraction:

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 |
+---------------------------+---------------------------+------------+--------------+--------------+