All Products
Search
Document Center

EXTRACT(datetime)

Last Updated: Jun 18, 2021

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