8.4Date and Time Functions

8.4.1DATEADD()

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

Table 8.56DATEADD Function Parameters
ParameterDescription

amount

An integer expression of the SMALLINT, INTEGER or BIGINT type. For unit MILLISECOND, the type is NUMERIC(18, 1). A negative value is subtracted.

unit

Date/time unit

datetime

An expression of the DATE, TIME or TIMESTAMP type

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.

8.4.1.1Examples 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 alsoSection 8.4.2, “DATEDIFF(), Operations Using Date and Time Values

8.4.2DATEDIFF()

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

Table 8.57DATEDIFF Function Parameters
ParameterDescription

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.

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

See alsoSection 8.4.1, “DATEADD(), Operations Using Date and Time Values

8.4.3EXTRACT()

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

Table 8.58EXTRACT Function Parameters
ParameterDescription

part

Date/time unit

datetime

An expression of the DATE, TIME or TIMESTAMP type

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

8.4.3.1Returned Data Types and Ranges

The returned data types and possible ranges are shown in the table below. If you try to extract a part that isn’t present in the date/time argument (e.g. SECOND from a DATE or YEAR from a TIME), an error occurs.

Table 8.59Types and ranges of EXTRACT results
PartTypeRangeComment

YEAR

SMALLINT

1-9999

 

MONTH

SMALLINT

1-12

 

QUARTER

SMALLINT

1-4

 

WEEK

SMALLINT

1-53

 

DAY

SMALLINT

1-31

 

WEEKDAY

SMALLINT

0-6

0 = Sunday

YEARDAY

SMALLINT

0-365

0 = January 1

HOUR

SMALLINT

0-23

 

MINUTE

SMALLINT

0-59

 

SECOND

NUMERIC(9,4)

0.0000-59.9999

includes millisecond as fraction

MILLISECOND

NUMERIC(9,1)

0.0-999.9

 

TIMEZONE_HOUR

SMALLINT

-23 - +23

 

TIMEZONE_MINUTE

SMALLINT

-59 - +59

 

8.4.3.1.1MILLISECOND

Extracts the millisecond value from a TIME or TIMESTAMP. The data type returned is NUMERIC(9,1).

If you extract the millisecond from Section 12.4, “CURRENT_TIME, be aware that this variable defaults to seconds precision, so the result will always be 0. Extract from CURRENT_TIME(3) or Section 12.5, “CURRENT_TIMESTAMP to get milliseconds precision.

8.4.3.1.2WEEK

Extracts the ISO-8601 week number from a DATE or TIMESTAMP. ISO-8601 weeks start on a Monday and always have the full seven days. Week 1 is the first week that has a majority (at least 4) of its days in the new year. The first 1-3 days of the year may belong to the last week (52 or 53) of the previous year. Likewise, a year’s final 1-3 days may belong to week 1 of the following year.

Caution

Be careful when combining WEEK and YEAR results. For instance, 30 December 2008 lies in week 1 of 2009, so extract(week from date '30 Dec 2008') returns 1. However, extracting YEAR always gives the calendar year, which is 2008. In this case, WEEK and YEAR are at odds with each other. The same happens when the first days of January belong to the last week of the previous year.

Please also notice that WEEKDAY is not ISO-8601 compliant: it returns 0 for Sunday, whereas ISO-8601 specifies 7.

See alsoData Types for Dates and Times

8.4.4FIRST_DAY()

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

Table 8.60FIRST_DAY Function Parameters
ParameterDescription

date_or_timestamp

Expression of type DATE, TIMESTAMP WITHOUT TIME ZONE or TIMESTAMP WITH TIME ZONE

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.

Note
  • The first day of the week is considered as Sunday, following the same rules as for Section 8.4.3, “EXTRACT() with WEEKDAY.

  • When a timestamp is passed, the return value preserves the time part.

8.4.4.1Examples of FIRST_DAY

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

8.4.5LAST_DAY()

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

Table 8.61LAST_DAY Function Parameters
ParameterDescription

date_or_timestamp

Expression of type DATE, TIMESTAMP WITHOUT TIME ZONE or TIMESTAMP WITH TIME ZONE

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.

Note
  • The last day of the week is considered as Saturday, following the same rules as for Section 8.4.3, “EXTRACT() with WEEKDAY.

  • When a timestamp is passed, the return value preserves the time part.

8.4.5.1Examples of LAST_DAY

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