Open In App

PLSQL | EXTRACT Function

Improve
Improve
Like Article
Like
Save
Share
Report

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 

Last Updated : 28 Oct, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads