DATEDIFF()

Tip

Find a more recent version at Firebird 5.0 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, however, look at all the bigger units. So:
- “datediff (day, date '26-Jun-1908', date '11-Sep-1973')” returns 23818
A negative result 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))