Oracle - EXTRACT - Extract Day, Month, Year, Hours, Minutes, Seconds etc

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:

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

• Reference and Articles
• Functions

Oracle 11g Release 2 SQL Language Reference

• EXTRACT for Datetime Function