Using EXTRACT Function In Oracle Database

EXTRACT:- This is oracle datetime function. Using this function extracts and returns the value of a specified datetime field from a datetime or interval expression.

EXAMPLE:-

SQL> select sysdate from dual;

SYSDATE
---------
13-MAY-14

SQL>
SQL> select extract(month from trunc(sysdate)) from dual;

EXTRACT(MONTHFROMTRUNC(SYSDATE))
--------------------------------
                               5

SQL> select extract(day from trunc(sysdate)) from dual;

EXTRACT(DAYFROMTRUNC(SYSDATE))
------------------------------
                            13

SQL> select extract(year from trunc(sysdate)) from dual;

EXTRACT(YEARFROMTRUNC(SYSDATE))
-------------------------------
                           2014
SQL> SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
                             1998

SQL> SELECT last_name, employee_id, hire_date, department_id
  2    FROM employees
  3    WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR')) > 2007
  4    AND department_id=80
  5    ORDER BY hire_date;

LAST_NAME                 EMPLOYEE_ID HIRE_DATE DEPARTMENT_ID
------------------------- ----------- --------- -------------
Johnson                           179 04-JAN-08            80
Marvins                           164 24-JAN-08            80
Zlotkey                           149 29-JAN-08            80
Lee                               165 23-FEB-08            80
Ande                              166 24-MAR-08            80
Kumar                             173 21-APR-08            80
Banda                             167 21-APR-08            80

7 rows selected.