EXTRACT Function

EXTRACT function gets the specified part (day, month, year, hours, minutes,second etc.) from a datetime value.

Syntax: EXTRACT(datetime_unit FROM datetime_expression1)

Although DATE contains time part but we can't extract the hour,minute and second from DATE.
HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

EXTRACT Function Details

Extract DAY from a specific DATE.

SELECT EXTRACT(DAY FROM DATE '2011-01-12') FROM dual;

Extract MONTH from a specific DATE.

SELECT EXTRACT(MONTH FROM DATE '2011-01-12') FROM dual;

Extract YEAR from a specific DATE.

SELECT EXTRACT(YEAR FROM DATE '2011-01-12') FROM dual;

We can't extract  hour,minute and second from DATE. We will get the error

SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;

HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

SELECT EXTRACT(HOUR FROM TIMESTAMP '2012-01-12 10:11:00') FROM dual;