Oracle Date/Time Function - Oracle/PLSQL EXTRACT Function






This Oracle tutorial explains how to use the Oracle/PLSQL EXTRACT function.

EXTRACT extracts a date or a timestamp and returns the year, month, day, hour, minute, second, or time zone.

We can extract YEAR, MONTH, and DAY from a DATE.

We can extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.

Syntax

The format:


EXTRACT({ YEAR | -- from www .  j a v  a 2 s. co m
          MONTH | 
          DAY |
          HOUR | 
          MINUTE | 
          SECOND } |
          { TIMEZONE_HOUR |
            TIMEZONE_MINUTE } |{ 
            TIMEZONE_REGION | }
            TIMEZONE_ABBR } FROM x)
KeywordDATETIME STAMPTIME STAMP WITH TIME ZONETIME STAMP WITH LOCAL TIME ZONEINTERVAL YEAR TO MONTHINTERVAL DAY TO SECOND
YEARXXXXX-
MONTHXXXXX-
DAYXXXX-X
HOUR-XXX-X
MINUTE-XXX-X
SECOND-XXX-X
TIMEZONE_HOUR--Xlocal session time zone data--
TIMEZONE_MINUTE--Xlocal session time zone data--
TIMEZONE_REGION--Xlocal session time zone data--
TIMEZONE_ABBR--Xlocal session time zone data--

The following code shows how to extract year/month/day from a date value.

EXTRACT(YEAR FROM DATE '2003-08-22')
------------------------------------
                                2003
 
EXTRACT(MONTH FROM DATE '2003-08-22')
------------------------------------
                                   8
 
EXTRACT(DAY FROM DATE '2003-08-22')
------------------------------------
                                  22




Example


SQL> SELECT EXTRACT(YEAR FROM TO_DATE('01-JAN-2011 19:15:26','DD-MON-YYYY HH24:MI:SS')) AS YEAR FROM dual;
-- ww  w  .j ava 2s. c  o m
      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>