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:
2027
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:
25
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.