How To Use Extract From Date In Oracle

How To Use Extract From Date In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle extract oracle extract in oracle online colleges online schools online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle extract oracle extract in oracle online colleges online schools online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle extract oracle extract in oracle online colleges online schools online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle extract oracle extract in oracle online colleges online schools online personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

See Also:
Oracle Select Oracle Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*