PLSQL | EXTRACT Function

The PLSQL EXTRACT function is used for extracting a specific value such as year, month, day or hour from a date or an interval value. Syntax:

EXTRACT(field FROM source)

Parameters Used: The EXTRACT function accepts two parameters :

• field - It is used to specify the component that needs to be extracted.
• source - It is used to specify a DATE, an INTERVAL, or a TIMESTAMP value from which a field is extracted.

The fields that can be extracted from various value types are:

• DATE: YEAR, MONTH and DAY.
• INTERVAL YEAR TO MONTH: YEAR and MONTH
• INTERVAL DAY TO SECOND: DAY, HOUR, MINUTE and SECOND.
• TIMESTAMP: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

Supported Versions of Oracle/PLSQL:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Example-1: Extracting the value of the YEAR field from a DATE value.

SELECT
  EXTRACT(YEAR FROM DATE '2019-10-26')
FROM
  DUAL; 

Output:

2019 

Example-2: Extracting the value of the month field from a DATE value.

SELECT
  EXTRACT(YEAR FROM DATE '2019-10-26')
FROM
  DUAL; 

Output:

10 

Example-3: Extracting the value of the day field from a DATE value.

SELECT
  EXTRACT(YEAR FROM DATE '2019-10-26')
FROM
  DUAL; 

Output:

26 

Example-4: Extracting the value of the year field from an INTERVAL YEAR TO MONTH.

SELECT
  EXTRACT( YEAR FROM INTERVAL '7-3' YEAR TO MONTH )
FROM
  DUAL; 

Output:

7 

In the above example, the mentioned argument '7-3' means 7 years and 3 months. Example-5: Extracting the value of the day field from an INTERVAL DAY TO SECOND.

SELECT
  EXTRACT( DAY FROM INTERVAL '26 10:32:29.53' DAY TO SECOND )
FROM
  dual; 

Output:

26 

In the above example, the mentioned argument '26 10:32:29.53' means 26 days, 10hours, 32 minutes and 29 seconds. Example-6: Extracting the value of the year field from a timestamp.

SELECT
  EXTRACT( YEAR FROM TIMESTAMP '2019-10-26 12:57:34.10' )
FROM
  dual; 

Output:

2019