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 
My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.