PL/SQL Extract

The Extract function extracts and returns a value from a datetime or interval value.

Extract syntax

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

Extract example

select extract(day from date '2014-12-21') from dual;

Result: 21


select extract(month from date '2014-12-21') from dual;

Result: 12


select extract(year from date '2014-12-21') from dual;

Result: 2014


select extract(day from sysdate) from dual;

Result: 10


select extract(month from sysdate) from dual;

Result: 1


select extract(year from sysdate) from dual;

Result: 2015


select extract(hour from to_timestamp(to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'))) from dual;

Result: 9


select extract(minute from to_timestamp(to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'))) from dual;

Result: 29


select extract(second from to_timestamp(to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'))) from dual;

Result: 58