Oracle EXTRACT (datetime) function

Extract date, time from a given datetime in Oracle

The EXTRACT() function is used to extract the value of a specified datetime field from a datetime or interval expression.

Syntax:

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

The expr can be any expression that evaluates to a datetime or interval datatype compatible with the requested field :

• If YEAR or MONTH is requested, then expr must evaluate to an expression of datatype DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.
• If DAY is requested, then expr must evaluate to an expression of datatype DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.
• If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of datatype TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here because Oracle Database treats it as ANSI DATE datatype, which has no time fields.
• If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of datatype TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Pictorial Presentation

Examples: Oracle EXTRACT (datetime)() function

The following example returns the year 2015.

SQL> SELECT EXTRACT(YEAR FROM DATE '2015-03-03') FROM DUAL;

Sample Output:

EXTRACT(YEARFROMDATE'2015-03-03')
---------------------------------
         2015

The following example selects all employees who were hired in and after 2008 :

Sample table: employees

SQL> SELECT first_name,last_name, employee_id, hire_date
  2    FROM employees
  3    WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR')) >= 2008
  4    ORDER BY hire_date;;

Sample Output:

FIRST_NAME           LAST_NAME                 EMPLOYEE_ID HIRE_DATE
-------------------- ------------------------- ----------- ---------
Charles              Johnson                           179 04-JAN-08
Douglas              Grant                             199 13-JAN-08
Mattea               Marvins                           164 24-JAN-08
Eleni                Zlotkey                           149 29-JAN-08
Girard               Geoni                             183 03-FEB-08
Hazel                Philtanker                        136 06-FEB-08
David                Lee                               165 23-FEB-08
Steven               Markle                            128 08-MAR-08
Sundar               Ande                              166 24-MAR-08
Amit                 Banda                             167 21-APR-08
Sundita              Kumar                             173 21-APR-08

11 rows selected.

Previous: DBTIMEZONE
Next: FROM_TZ