- Categories:
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:
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 valuedate_or_time_expr1
is subtracted fromdate_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
isweek
(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
anddate_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
anddate_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 | +---------------------------+---------------------------+------------+--------------+--------------+