Oracle EXTRACT function.

Preamble

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

Oracle/PLSQL syntax of EXTRACT function

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

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.

• Function EXTRACT returns VARCHAR2 when parameters TIMEZONE_REGION or TIMEZONE_ABBR are provided (because time zone name or abbreviation information is returned).
• You can only extract YEAR, MONTH, and DAY from the date.
• You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from date/time with time zone data type.

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

Let’s consider some examples of EXTRACT function and learn how to use EXTRACT function in Oracle.

SELECT EXTRACT (YEAR FROM DATE '2019-08-22') FROM DUAL;

--Result: 2019

SELECT EXTRACT (MONTH FROM DATE '2019-08-22') FROM DUAL;

--Result: 8

SELECT EXTRACT (DAY FROM DATE '2019-08-22') FROM DUAL;

--Result: 22

SQL: Extract function

About Enteros

​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​