Adds or subtracts datetime units from a datetime value

Result typeDATE, TIME or TIMESTAMP

Syntax

   |DATEADD (<args>)
   | 
   |<args> ::=
   |    <amount> <unit> TO <datetime>
   |  | <unit>, <amount>, <datetime>
   | 
   |<amount> ::= an integer expression (negative to subtract)
   |<unit> ::=
   |    YEAR | MONTH | WEEK | DAY
   |  | HOUR | MINUTE | SECOND | MILLISECOND
   |<datetime> ::= a DATE, TIME or TIMESTAMP expression

Adds the specified number of years, months, weeks, days, hours, minutes, seconds or milliseconds to a date/time value.

• The result type is determined by the third argument.

• With TIMESTAMP and DATE arguments, all units can be used.

• With TIME arguments, only HOUR, MINUTE, SECOND and MILLISECOND can be used.

  |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

Difference between two datetime values in a datetime unit

Result typeBIGINT, 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

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.

  |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))

Extracts a datetime unit from a datetime value

Result typeSMALLINT or NUMERIC

Syntax

  |EXTRACT (<part> FROM <datetime>)
  | 
  |<part> ::=
  |    YEAR | MONTH | QUARTER | WEEK
  |  | DAY | WEEKDAY | YEARDAY
  |  | HOUR | MINUTE | SECOND | MILLISECOND
  |  | TIMEZONE_HOUR | TIMEZONE_MINUTE
  |<datetime> ::= a DATE, TIME or TIMESTAMP expression

Extracts and returns an element from a DATE, TIME or TIMESTAMP expression.

Returns the first day of a time period containing a datetime value

Result TypeDATE, TIMESTAMP (with or without time zone)

Syntax

  |FIRST_DAY(OF <period> FROM date_or_timestamp)
  | 
  |<period> ::= YEAR | MONTH | QUARTER | WEEK

FIRST_DAY returns a date or timestamp (same as the type of date_or_timestamp) with the first day of the year, month or week of a given date or timestamp value.

  |select
  |  first_day(of month from current_date),
  |  first_day(of year from current_timestamp),
  |  first_day(of week from date '2017-11-01'),
  |  first_day(of quarter from date '2017-11-01')
  |from rdb$database;

Returns the last day of a time period containing a datetime value

Result TypeDATE, TIMESTAMP (with or without time zone)

Syntax

  |LAST_DAY(OF <period> FROM date_or_timestamp)
  | 
  |<period> ::= YEAR | MONTH | QUARTER | WEEK

LAST_DAY returns a date or timestamp (same as the type of date_or_timestamp) with the last day of the year, month or week of a given date or timestamp value.

  |select
  |  last_day(of month from current_date),
  |  last_day(of year from current_timestamp),
  |  last_day(of week from date '2017-11-01'),
  |  last_day(of quarter from date '2017-11-01')
  |from rdb$database;