How To Use Extract From Date In Oracle

This online tutorial is based on examples to make it easier to follow. Oracle EXTRACT is a function of ANSI standard and it returns a specified part of value from a date or timestamp. The syntax of Oracle EXTRACT is:

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

The following list is about Oracle Extract combinations that are valid for “returning value type” and “date type“:

• when “returning value type” is YEAR or MONTH then “date type” can be only 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 only 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 only 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 only TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE

The first example returns a number of the current month. To receive the current month value we are using the Oracle Sysdate function. The example date is November 27th 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 will extract out from the sysdate function a year number and it is in the first column “2012“.

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

The following query will extract the number of hours from the Oracle timestamp. We are using timestamp and NOT a date because Hours, Minutes and Seconds can be taken out only from a timestamp type (see the valid combinations list above). On that moment we had 12pm and the Hours column shows the same number.

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

The fourth example will show a valid combination that logically and technically is invalid. You cannot extract a time zone from Oracle timestamp and the reason is quite simple. One time offset have been assigned more than one time zone and Oracle database doesn’t know which timezone region name it should to return. The only output you would see in this case is “UNKNOWN“.

SELECT EXTRACT (TIMEZONE_REGION FROM systimestamp)
   FROM DUAL;

See Also:
Oracle Select Oracle Home