EXTRACT IN ORACLE - Online Tech Support

This page is based on examples to be easier to follow. The Oracle Extract is ANSI standard function and it returns a specified value from a date value. The Oracle Extract syntax is:

EXTRACT ( <returning value type> FROM <date type>)

Only the following “returning value type” and “date type” combinations are valid:

when “returning value type” is YEAR or MONTH then “date type” can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH
when “returning value type” is DAY then “date type” can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND
when “returning value type” is HOUR, MINUTE or SECOND then “date type” can be TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND
when “returning value type” is TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION or TIMEZONE_OFFSET then “date type” can be TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE

The first example with Oracle Extract will return a number of the current month. The example date is November 27th of 2012 and as November is 11th month in the year the Month column shows number “11“.

SELECT EXTRACT (MONTH FROM SYSDATE) AS Month, SYSDATE
  FROM DUAL;

The second example takes out a year number from the date. The sample output is in the first column “2012” and the second column shows the current date to make this example more understandable.

SELECT EXTRACT (YEAR FROM SYSDATE) AS YEAR, SYSDATE
  FROM DUAL;

The third SQL query extracts the number of hours from the Oracle timestamp. We are using a timestamp and not date because Hours, Minutes and Seconds can be extract only from a timestamp type (see the valid combinations list above). That moment we had 12pm and the Hours column shows the same number.

SELECT EXTRACT (HOUR FROM SYSTIMESTAMP) AS HOURS, SYSTIMESTAMP
  FROM DUAL;

This example below will show you a valid combination that logically is not valid. In short, you cannot extract a time zone from Oracle timestamp. The reason is quite simple one time offset have been assigned more than one time zone and Oracle database doesn’t which time-zone region name to return. The only output you would see in this case is “UNKNOWN“.

SELECT EXTRACT (TIMEZONE_REGION FROM systimestamp)
   FROM DUAL;