Oracle PL/SQL EXTRACT Function

Oracle EXTRACT Function is used to extract a value from a date or interval value. SQL Extract Function can be used with String Dates and even with SQL SYSDATE.

Oracle EXTRACT Function Syntax

SELECT EXTRACT(
[YEAR or MONTH or DAY or HOUR or MINUTE or SECOND]
[TIMEZONE_HOUR or TIMEZONE_MINUTE]
[TIMEZONE_REGION or TIMEZONE_ABBR]
FROM [date_value or interval._value]
) FROM table_name;

• Only Year, Month or Day can be extracted from a DATE value.
• Only timezone_hour or timezone_minute can be extracted from a time stamp with time zone value.

Oracle EXTRACT Function – Extracting YEAR Example

We can use the SQL EXTRACT Function to extract year from a date passed as string.

For example, the EXTRACT Function query below extracts the YEAR from passed String.

SELECT EXTRACT(YEAR FROM DATE '2010-10-12') "PLSQL Extract"
FROM DUAL;

The above SQL EXTRACT query returns “2010”

Note: We have aliased EXTRACT(YEAR FROM DATE ‘2010-10-12’) as PLSQL Extract

Oracle EXTRACT Function – Extracting MONTH Example

We can use the SQL EXTRACT Function to extract MONTH from date passeda s String,

For example, the SQL EXTRACT Function query below extracts the MONTH from passed String.

SELECT EXTRACT(MONTH FROM DATE '2010-10-12') "PLSQL Extract"
FROM DUAL;

The above Oracle EXTRACT query returns “10” as the month.

Note: We have aliased EXTRACT(MONTH FROM DATE ‘2010-10-12’) as PLSQL Extract

Oracle EXTRACT Function – Extracting DAY Example

Apart from extracting YEAR and MONTH, SQL EXTRACT Function can be used to extract DAY.

For example, the SQL EXTRACT Function query below extracts DAY from the parameter passed.

SELECT EXTRACT(DAY FROM DATE '2010-10-12') "PLSQL Extract"
FROM DUAL;

The above Oracle EXTRACT query returns “12” as the DAY of the MONTH.

Note: We have aliased EXTRACT(DAY FROM DATE ‘2010-10-12’) as PLSQL Extract.

Oracle EXTRACT Function – Using SYSDATE Example

SQL SYSDATE can also be used with the SQL EXTRACT Function to get YEAR, MONTH or DAY.

For example, the Oracle EXTRACT query below returns the YEAR using SYSDATE as parameter.

SELECT EXTRACT(YEAR FROM SYSDATE)
FROM DUAL;

May return “2012” depending upon SYSDATE.