totn Oracle Functions

Oracle / PLSQL: EXTRACT Function

This Oracle tutorial explains how to use the Oracle/PLSQL EXTRACT function with syntax and examples.

Description

The Oracle/PLSQL EXTRACT function extracts a value from a date or interval value.

Syntax

The syntax for the EXTRACT function in Oracle/PLSQL is:

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

Note

  • You can only extract YEAR, MONTH, and DAY from a DATE.
  • You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.

Returns

The EXTRACT function returns a numeric value when the following parameters are provided: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_MINUTE.
The EXTRACT function returns a VARCHAR2 when TIMEZONE_REGION or TIMEZONE_ABBR parameters are provided (because the time zone name or abbreviation information is returned).

Applies To

The EXTRACT function can be used in the following versions of Oracle/PLSQL:

  • Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Example

Let's look at some Oracle EXTRACT function examples and explore how to use the EXTRACT function in Oracle/PLSQL.

For example:

EXTRACT(YEAR FROM DATE '2003-08-22')
Result: 2003

EXTRACT(MONTH FROM DATE '2003-08-22')
Result: 8

EXTRACT(DAY FROM DATE '2003-08-22')
Result: 22
Share on: