FirebirdSQL logo
 Procedural SQL (PSQL) StatementsAggregate Functions 

Examples of DATEADD

dateadd (28 day to current_date)
dateadd (-6 hour to current_time)
dateadd (month, 9, DateOfConception)
dateadd (-38 week to DateOfBirth)
dateadd (minute, 90, cast('now' as time))
dateadd (? year to date '11-Sep-1973')
select
  cast(dateadd(-1 * extract(millisecond from ts) millisecond to ts) as varchar(30)) as t,
  extract(millisecond from ts) as ms
from (
  select timestamp '2014-06-09 13:50:17.4971' as ts
  from rdb$database
) a
T                        MS
------------------------ ------
2014-06-09 13:50:17.0000  497.1
See also

[fblangref50-scalarfuncs-datediff], Operations Using Date and Time Values

DATEDIFF()

Difference between two datetime values in a datetime unit

Result type

BIGINT, or NUMERIC(18,1) for MILLISECOND

Syntax
DATEDIFF (<args>)

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

<unit> ::=
    YEAR | MONTH | WEEK | DAY
  | HOUR | MINUTE | SECOND | MILLISECOND
<momentN> ::= a DATE, TIME or TIMESTAMP expression
Table 1. DATEDIFF Function Parameters
Parameter Description

unit

Date/time unit

moment1

An expression of the DATE, TIME or TIMESTAMP type

moment2

An expression of the DATE, TIME or TIMESTAMP type

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

  • DATE and TIMESTAMP arguments can be combined.No other mixes are allowed.

  • With TIMESTAMP and DATE arguments, all units 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.