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.

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 DateTimeField and DateTimExpression or IntervalExpression 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 dual system table.

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