The EXTRACT(datetime) function extracts elements such as the year, month, day, hour, minute, and second values from a specified time field or expression.
Syntax
EXTRACT (fields FROM datetime)
Parameters
| Parameter | Description |
|---|---|
| fields | The name of the element to be extracted. Valid values: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, and TIMEZONE_ABBR. |
| datetime | A value of the data types such as DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND. |
Return type
When this function extracts the TIMEZONE_REGION and TIMEZONE_ABBR elements, the return type is VARCHAR2. When this function extracts other elements, the return type is NUMBER.
Examples
Execute the following statement:
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-2-16 2:38:40') Hour,
EXTRACT(MINUTE FROM TIMESTAMP '2001-2-16 2:38:40 ') Minute,
EXTRACT(SECOND FROM TIMESTAMP '2001-2-16 2:38:40 ') Second,
EXTRACT(DAY FROM TIMESTAMP '2001-2-16 2:38:40 ') Day,
EXTRACT(MONTH FROM TIMESTAMP '2001-2-16 2:38:40 ') Month,
EXTRACT(YEAR FROM TIMESTAMP '2001-2-16 2:38:40 ') Year
FROM DUAL;
The following result is returned:
+--------+--------+------+------+------+------+
| Hour | Minute | Second | Day | Month | Year |
+--------+--------+------+------+------+------+
| 2 | 38 | 40 | 16 | 2 | 2001 |
+--------+--------+------+------+------+------+