EXTRACT function extracts and returns the year, month, day, hour, minute, second, or time zone from Z, Z may be a timestamp or a DATE.
The Syntax of the EXTRACT Function is:
EXTRACT({ YEAR | MONTH | DAY |HOUR | MINUTE | SECOND } |
{ TIMEZONE_HOUR |TIMEZONE_MINUTE } |{ TIMEZONE_REGION | }
TIMEZONE_ABBR }FROM Z)
This query returns Day ,Month and Year from the Hire_date field.
SELECT First_Name,Last_Name,EXTRACT(Day from Hire_date)
as Day,EXTRACT(Month from Hire_date) as Month,
EXTRACT(Year from Hire_date) as Year from employees
as Day,EXTRACT(Month from Hire_date) as Month,
EXTRACT(Year from Hire_date) as Year from employees
Result
This query display the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP():
SELECT
EXTRACT(HOUR FROM TO_TIMESTAMP(’07-May-2011 15:18:27′,’DD-MON-YYYY HH24:MI:SS’)) AS HOUR,
EXTRACT(MINUTE FROM TO_TIMESTAMP(’07-May-2011 15:18:27′,’DD-MON-YYYY HH24:MI:SS’)) AS MINUTE,
EXTRACT(SECOND FROM TO_TIMESTAMP(’07-May-2011 15:18:27′,’DD-MON-YYYY HH24:MI:SS’)) AS SECOND
FROM dual;
Result :
