How To Use Oracle EXTRACT
  Home
 

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;

online tech support computer help computer technician computer problems computer oracle extract oracle extract in oracle online schools oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle extract oracle extract in oracle online schools oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle extract oracle extract in oracle online schools oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle extract oracle extract in oracle online schools oracle sql database programming database oracle retirement planning retirement sql database oracle database sql


See Also:
Oracle Select Oracle Date Format Oracle Date Difference Home

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*