FB

Oracle EXTRACT FunctionThe Oracle EXTRACT function is very useful when you’re doing any work with a date field or date expression. Let’s learn what it is and see some examples in this article.

Purpose of the Oracle EXTRACT Function

The EXTRACT function extracts a specific datetime value from a date or interval value.

This means you can easily get the month, year, day, or any other component of a datetime value. It’s easier than doing conversions using other functions such as TO_CHAR.

I think EXTRACT is one of the most useful Oracle SQL date functions to learn.

Note: There is another EXTRACT function for XML, but this is not covered in this article.

 

Syntax

The syntax for the Oracle EXTRACT function is:

EXTRACT ( date_component FROM expression )

 

Parameters

The parameters of the EXTRACT function are:

  • date_component (mandatory): This is the part of the datetime part of the expression you wish to extract. It can be any of YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, or TIMEZONE_ABBR. It should not be included in quotes as they are Oracle data types.
  • expression (mandatory): This is the expression that the datetime value is being retrieved from. It can be a datetime or an interval data type. It can be a column or an expression.

Some things to note about this function:

  • When you extract from a datetime with a timezone value, the value that is returned is in the UTC time zone.
  • If Oracle cannot determine a value from the specified expression, it will return UNKNOWN.
  • You can only extract YEAR, MONTH, and DAY from a DATE value.
  • You can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from TIMESTAMP WITH TIME ZONE data types.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Examples of the Oracle EXTRACT Function

Here are some examples of the EXTRACT function. I find that examples are the best way for me to learn about code, even with the explanation above.

 

Example 1 – Extract Year from Date

This example shows how you can get the year from a date in Oracle using EXTRACT.

SELECT SYSDATE,
EXTRACT(YEAR FROM SYSDATE) as EXTRACT_EXAMPLE
FROM dual;

Result:

SYSDATE EXTRACT_EXAMPLE
23/DEC/2015 2015

 

Example 2 – Extract Month from Date

This example shows how you can get the month from a date in Oracle using EXTRACT.

SELECT SYSDATE,
EXTRACT(MONTH FROM SYSDATE) as EXTRACT_EXAMPLE
FROM dual;

Result:

SYSDATE EXTRACT_EXAMPLE
23/DEC/2015 12

 

Example 3 – Extract Day from Date

This example shows how you can get the day from a date in Oracle using EXTRACT.

SELECT SYSDATE,
EXTRACT(DAY FROM SYSDATE) as EXTRACT_EXAMPLE
FROM dual;

Result:

SYSDATE EXTRACT_EXAMPLE
23/DEC/2015 23

 

Example 4 – Extract Hour from Date

This example shows what happens when you try to extract a time component from a date value.

SELECT SYSDATE,
EXTRACT(HOUR FROM SYSDATE) as EXTRACT_HOUR
FROM dual;

Result:

ORA-30076: invalid extract field for extract source
30076. 00000 - "invalid extract field for extract source"
*Cause: The extract source does not contain the specified extract field.

This error is shown because the SYSDATE only includes YEAR, MONTH and DAY components, and we are trying to extract the HOUR component from it.

 

Example 5 – Extract Hour, Minute and Second

This example shows how you can use the Oracle EXTRACT function to find the hour, minute, or second from a value.

SELECT SYSTIMESTAMP,
EXTRACT(HOUR FROM SYSTIMESTAMP) as EXTRACT_HOUR,
EXTRACT(MINUTE FROM SYSTIMESTAMP) as EXTRACT_MINUTE,
EXTRACT(SECOND FROM SYSTIMESTAMP) as EXTRACT_SECOND
FROM dual;

Result:

SYSTIMESTAMP EXTRACT_HOUR EXTRACT_MINUTE EXTRACT_SECOND
23/DEC/15 09:43:07.203000000 AM +11:00 22 43 7.203

The minutes and seconds are showing the same as the SYSTIMESTAMP value, but why is the HOUR different?

This is due to the time zone.

You can see that the SYSTIMESTAMP shows the time as 9AM with the timezone +11 hours.

If you remove the timezone by subtracting the 11 hours, you get to the UTC timezone. This is what the EXTRACT function does.

So, 11 hours before 9AM is 10PM the previous evening. This is reflected as an HOUR value of 22.

 

Example 6 – Timezone Information

This example shows how you can use EXTRACT with TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, and TIMEZONE_ABBR.

SELECT SYSTIMESTAMP,
EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) as EXTRACT_TIMEZONE_HOUR,
EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) as EXTRACT_TIMEZONE_MINUTE,
EXTRACT(TIMEZONE_REGION FROM SYSTIMESTAMP) as EXTRACT_TIMEZONE_REGION,
EXTRACT(TIMEZONE_ABBR FROM SYSTIMESTAMP) as EXTRACT_TIMEZONE_ABBR
FROM dual;

Result:

SYSTIMESTAMP EXTRACT_TIMEZONE_HOUR EXTRACT_TIMEZONE_MINUTE EXTRACT_TIMEZONE_REGION EXTRACT_TIMEZONE_ABBR
23/DEC/15 09:43:07.203000000 AM +11:00 11 0 UNKNOWN UNK

You can see that the TIMEZONE_HOUR and TIMEZONE_MINUTE reflect the timezone offset for the SYSTIMESTAMP value.

However, the TIMEZONE_REGION is set to UNKNOWN, because there are multiple regions with the same 11 hour timezone offset.

The TIMEZONE_ABBR also has the same issue, as there are multiple timezone regions with the same offset.

 

Example 7 – Using Interval

This example shows how you can use the Oracle EXTRACT function on an interval data type.

SELECT TO_YMINTERVAL('03-02'),
EXTRACT(YEAR FROM TO_YMINTERVAL('03-02')) as EXTRACT_YEAR,
EXTRACT(MONTH FROM TO_YMINTERVAL('03-02')) as EXTRACT_MONTH
FROM dual;

Result:

TO_YMINTERVAL(’03-02′) EXTRACT_YEAR EXTRACT_MONTH
+03-02 3 2

The YMINTERTVAL data type is made up of a Year and Month value, which have been extracted here.

 

Similar Functions

Some functions which are similar to the Oracle EXTRACT function are:

  • TO_DATE – This function converts a string to a date value.
  • TO_CHAR – This function converts a value to a varchar or string value.
  • TRUNC – This function, when performed on dates, removes the time portion and only shows a date.

You can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your Free PDF: 9 Ways to Improve your Database Skills

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your SQL Function Cheat Sheet Now: