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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join