EXTRACT

The EXTRACT function extracts and returns the value of a specified datetime field from a datetime or interval value expression as a NUMBER data type. This function can be useful for manipulating datetime field values in very large tables.

If you are using TimesTen type mode, for information on the EXTRACT function, refer to documentation from previous releases of TimesTen.

SQL syntax

EXTRACT (DateTimeField FROM IntervalExpression | DateTimeExpression)

Parameters

EXTRACT has the following parameters:

Parameter	Description
`DateTimeField`	The field to be extracted from `IntervalExpression` or `DateTimeExpression`. Accepted fields are YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.
`IntervalExpression`	An interval result.
`DateTimeExpression`	A datetime expression. For example, TIME, DATE, TIMESTAMP.

Description

Some combinations of DateTime field and DateTime or interval value expression result in ambiguity. In these cases, TimesTen returns UNKNOWN.
The field you are extracting must be a field of the IntervalExpression or DateTimeExpression. For example, you can extract only YEAR, MONTH, and DAY from a DATE value. Likewise, you can extract HOUR, MINUTE or SECOND only from the TIME, DATE, or TIMESTAMP data type.
The fields are extracted into a NUMBER value.

Examples

The following example extracts the second field out of the interval result sysdate-t1.createtime

SELECT EXTRACT(SECOND FROM sysdate-t1.createtime) FROM t1;

The following example extracts the second field out of sysdate from the system table DUAL.

Command> SELECT EXTRACT (SECOND FROM sysdate) FROM dual;
< 20 >
1 row found.