EXTRACT

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

SQL> SELECT EXTRACT(YEAR FROM TO_DATE('01-JAN-2011 19:15:26','DD-MON-YYYY HH24:MI:SS')) AS YEAR FROM dual;

      YEAR
----------
      2011

SQL> SELECT EXTRACT(MONTH FROM TO_DATE('01-JAN-2011 18:15:26','DD-MON-YYYY HH24:MI:SS')) AS MONTH FROM dual;

     MONTH
----------
         1

SQL> SELECT EXTRACT(DAY FROM TO_DATE('01-JAN-2011 17:15:26','DD-MON-YYYY HH24:MI:SS')) AS DAY FROM dual;

       DAY
----------
         1

SQL> SELECT EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2011 18:14:21','DD-MON-YYYY HH24:MI:SS')) AS HOUR FROM dual;

      HOUR
----------
        18

SQL> SELECT EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2011 18:14:21','DD-MON-YYYY HH24:MI:SS')) AS MINUTE FROM dual;

    MINUTE
----------
        14

SQL> SELECT EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2011 18:14:21','DD-MON-YYYY HH24:MI:SS')) AS SECOND FROM dual;

    SECOND
----------
        21

SQL> SELECT EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ('01-JAN-2011 18:14:21 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM')) AS TZH FROM dual;

       TZH
----------
        -7

SQL> SELECT EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ('01-JAN-2011 18:14:21 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM')) AS TZM FROM dual;

       TZM
----------
       -15

SQL> SELECT EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ('01-JAN-2011 18:14:21 PST', 'DD-MON-YYYY HH24:MI:SS TZR')) AS TZR FROM dual;

TZR
----------------------------------------------------------------
PST

SQL> SELECT EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ('01-JAN-2011 18:14:21 PST', 'DD-MON-YYYY HH24:MI:SS TZR')) AS TZA FROM dual;

TZA
----------
PST

SQL>