EXTRACT (datetime) Function in Oracle

In Oracle Database, the EXTRACT(datetime) function extracts and returns the value of a specified datetime field from a datetime or interval expression.

Syntax

The syntax goes like this:

EXTRACT( { YEAR
         | MONTH
         | DAY
         | HOUR
         | MINUTE
         | SECOND
         | TIMEZONE_HOUR
         | TIMEZONE_MINUTE
         | TIMEZONE_REGION
         | TIMEZONE_ABBR
         }
         FROM { expr }
       )

Where expr is any expression that evaluates to a datetime or interval data type compatible with the requested field.

The expr argument is treated as an ANSI datetime data type.

Example 1

Here’s an example to demonstrate the function:

SELECT EXTRACT(YEAR FROM DATE '2027-10-03')
FROM DUAL;

Result:

Example 2

Here’s an example that extracts the seconds and microseconds from a TIMESTAMP value:

SELECT 
    EXTRACT(SECOND FROM TIMESTAMP '2027-10-15 23:10:57.98321 +04:00') AS Result
FROM DUAL;

Result:

57.98321

Intervals

Here’s an example that extracts the year from an interval literal:

SELECT EXTRACT(YEAR FROM INTERVAL '25-3' YEAR TO MONTH)
FROM DUAL;

Result:

Attempting to Extract the Wrong Field

Here’s what happens when we try to extract a field that doesn’t exist in the specified value:

SELECT 
    EXTRACT(SECOND FROM DATE '2027-10-15') AS Result
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT 
    EXTRACT(SECOND FROM DATE '2027-10-15') AS Result
FROM DUAL
Error at Command Line : 2 Column : 25
Error report -
SQL Error: ORA-30076: invalid extract field for extract source
30076. 00000 -  "invalid extract field for extract source"
*Cause:    The extract source does not contain the specified extract field.
*Action:

Not all date parts can be extracted from all data types. See the Oracle documentation for the accepted date parts for each given data type.