EXTRACT function gets the specified part (day, month, year, hours, minutes etc.) from a datetime value.
Quick Example:
Get the day from January 12, 2011:
SELECT EXTRACT(DAY FROM DATE '2011-01-12') FROM dual; -- Result: 12
Summary information:
Syntax | EXTRACT(datetime_unit FROM datetime_expression1) |
Datetime Units | YEAR, MONTH, DAY, HOUR, MINUTE and SECOND (full...) |
Time Units | Although DATE contains time part, HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only |
Last Update: Oracle 11g Release 2
Although DATE data type contains a time part in Oracle, you cannot use EXTRACT function to get HOUR, MINUTE or SECOND from a DATE value:
-- Any DATE value contains time part in Oracle SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual; -- Result: 12-JAN-2012 12:06:17 -- But you cannot extract it SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual; -- ERROR at line 1: -- ORA-30076: invalid extract field for extract source
So HOUR, MINUTE and SECOND units can be extracted from TIMESTAMP values only:
SELECT EXTRACT(HOUR FROM TIMESTAMP '2012-01-12 10:11:00') FROM dual; -- Result: 10
Oracle 11g Release 2 SQL Language Reference