DATEDIFF()

<< | FB 2.1 Language Reference | >>

DATEDIFF()

Available in: DSQL, PSQL

Added in: 2.1

Description

Returns the number of years, months, days, hours, minutes, seconds or milliseconds elapsed between two date/time values.

Result type: BIGINT

Syntax 

 DATEDIFF (<args>)

 <args>    ::= <unit> FROM <moment1> TO <moment2>
               | <unit>, <moment1>, <moment2>

 <unit>    ::= YEAR | MONTH | DAY
               | HOUR | MINUTE | SECOND | MILLISECOND
 <momentN> ::= a DATE, TIME or TIMESTAMP expression


  • DATE and TIMESTAMP arguments can be combined. No other mixes are allowed.
  • With DATE arguments, only YEAR, MONTH and DAY can be used.
  • With TIME arguments, only HOUR, MINUTE, SECOND and MILLISECOND can be used.

Computation

  • DATEDIFF doesn't look at any smaller units than the one specified in the first argument. As a result,
    • datediff (year, date '1-Jan-2009', date '31-Dec-2009') returns 0, but
    • datediff (year, date '31-Dec-2009', date '1-Jan-2010') returns 1
  • It does look, however, at all the bigger units. So:
    • datediff (day, date '26-Jun-1908', date '11-Sep-1973') returns 23818
  • A negative return value indicates that moment2 lies before moment1.

Examples 

 datediff (hour from current_timestamp to timestamp '12-Jun-2059 06:00')
 datediff (minute from time '0:00' to current_time)
 datediff (month, current_date, date '1-1-1900')
 datediff (day from current_date to cast(? as date))

back to top of page
<< | FB 2.1 Language Reference | >>