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)

Keyword	DATE	TIME STAMP	TIME STAMP WITH TIME ZONE	TIME STAMP WITH LOCAL TIME ZONE	INTERVAL YEAR TO MONTH	INTERVAL DAY TO SECOND
YEAR	X	X	X	X	X	-
MONTH	X	X	X	X	X	-
DAY	X	X	X	X	-	X
HOUR	-	X	X	X	-	X
MINUTE	-	X	X	X	-	X
SECOND	-	X	X	X	-	X
TIMEZONE_HOUR	-	-	X	local session time zone data	-	-
TIMEZONE_MINUTE	-	-	X	local session time zone data	-	-
TIMEZONE_REGION	-	-	X	local session time zone data	-	-
TIMEZONE_ABBR	-	-	X	local 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>