Skip to main content
Last updated on

date_diff function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 13.3 LTS and above

Returns the difference between two dates in days, or between two timestamps in a specified unit.

Syntax​

date_diff(endDate, startDate)

date_diff(unit, start, end)

unit
 { MICROSECOND |
   MILLISECOND |
   SECOND |
   MINUTE |
   HOUR |
   DAY |
   WEEK |
   MONTH |
   QUARTER |
   YEAR }

Arguments​

  • endDate: An ending DATE expression.
  • startDate: A starting DATE expression.
  • unit: A unit of measure.
  • start: A starting TIMESTAMP expression.
  • end: An ending TIMESTAMP expression.

Returns​

  • For date_diff(endDate, startDate), an INTEGER that represents the number of days from startDate to endDate.
  • For date_diff(unit, start, end), a BIGINT that represents the difference between start and end measured in units. This form is a synonym for timestampdiff function.

The result is negative if the starting value is greater than the ending value.

The function counts whole elapsed units based on UTC with a DAY being 86400 seconds.

One month is considered elapsed when the calendar month has increased and the calendar day and time is equal or greater to the start. Weeks, quarters, and years follow from that.

Examples​

SQL
> SELECT date_diff(DATE'2009-07-31', DATE'2009-07-30');
 1

-- One second shy of a month elapsed
> SELECT date_diff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 11:59:59');
 0

-- One month has passed even though its' not end of the month yet because day and time line up.
> SELECT date_diff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 12:00:00');
 1

-- Start is greater than the end
> SELECT date_diff(YEAR, DATE'2021-01-01', DATE'1900-03-28');
 -120
On this page