Skip to main content

DATEDIFF function

Returns the span between two dates based on the specified date part.

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 these will be truncated to a DATE.

  • end: A DATE. Also accepts a TIMESTAMP or TIMESTAMP_TZ, but these will be truncated to a DATE.

Returns

A BIGINT. The calculation logic for rounding the difference varies by environment:

  • Vertica: The ceiling of the absolute value is used when rounding the difference.

  • Spark-based engine: The floor value is used when rounding the difference.

Examples

-- Standard calculation of days
SELECT DATEDIFF(DAY, DATE '2001-02-03', DATE '2001-02-06');
-- Result: 3

-- Example showing logic differences in calculation:
SELECT DATEDIFF(MONTH, DATE '2001-01-15', DATE '2001-03-01');
-- Result: 2 (Vertica)
-- Result: 1 (Spark-based engine)

-- Example showing date truncation (hours are ignored as timestamps are treated as dates)
SELECT DATEDIFF(HOUR, TIMESTAMP '2001-02-03 04:00:00', TIMESTAMP '2001-02-03 05:00:00');
-- Result: 0