FirebirdSQL logo

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

EXTRACT()

Extracts a datetime unit from a datetime value

Result type

SMALLINT 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 1. EXTRACT Function Parameters
Parameter Description

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.

Returned 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 1. Types and ranges of EXTRACT results
Part Type Range Comment

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

 

MILLISECOND

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

Note

If you extract the millisecond from [fblangref50-contextvars-current-time], be aware that this variable defaults to seconds precision, so the result will always be 0.Extract from CURRENT_TIME(3) or [fblangref50-contextvars-current-timestamp] to get milliseconds precision.