Skip to main content

DATEDIFF function

Returns the span between two dates.

Syntax

DATEDIFF(datepart, start, end)

Arguments

  • datepart: one of YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, or MICROSECOND.

  • start: a DATE. Also accepts a TIMESTAMP or TIMESTAMP_TZ, but those will be truncated into a DATE.

  • end: a DATE. Also accepts a TIMESTAMP or TIMESTAMP_TZ, but those will be truncated into a DATE.

Returns

A BIGINT. In Vertica, the ceiling of the absolute value will be used when rounding the difference. In Databricks, the floor value will be used.

Examples

> SELECT DATEDIFF(DAY, DATE '2001-02-03', DATE '2001-02-06');
 3
> SELECT DATEDIFF(MONTH, DATE '2001-01-15', DATE '2001-03-01');
 2 (Vertica)
 1 (Databricks)
> SELECT DATEDIFF(HOUR, TIMESTAMP '2001-02-03 04:00:00', TIMESTAMP '2001-02-03 05:00:00');
 0