PL/SQL Extract

The PL/SQL EXTRACT function is a powerful tool that allows developers to extract a specific part of a date or timestamp value in Oracle databases. It is a built-in function in Oracle’s PL/SQL language and is used to retrieve individual components such as year, month, day, hour, minute, and second from a given date or timestamp.

Syntax

The syntax of the EXTRACT function is as follows:

EXTRACT (field FROM date_expression)

Here, the field parameter is a keyword that specifies the component of the date or timestamp that needs to be extracted. It can take one of the following values:

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • TIMEZONE_HOUR
  • TIMEZONE_MINUTE

The date_expression parameter is the date or timestamp value from which the specified component needs to be extracted.

Example

Let’s take an example to understand how the EXTRACT function works:

select extract(day from date '2014-12-21') from dual;
Result: 21

select extract(month from date '2014-12-21') from dual;
Result: 12

select extract(year from date '2014-12-21') from dual;
Result: 2014

select extract(day from sysdate) from dual;
Result: 10

select extract(month from sysdate) from dual;
Result: 1

select extract(year from sysdate) from dual;
Result: 2015

select extract(hour from to_timestamp(to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'))) from dual;
Result: 9

select extract(minute from to_timestamp(to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'))) from dual;
Result: 29

select extract(second from to_timestamp(to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss'))) from dual;
Result: 58

In addition to the above-mentioned components, the EXTRACT function can also extract the timezone offset from the date or timestamp value. The TIMEZONE_HOUR and TIMEZONE_MINUTE keywords are used to extract the hour and minute components of the timezone offset, respectively.

In conclusion, the PL/SQL EXTRACT function is a useful tool for developers working with date and timestamp values in Oracle databases. It allows them to extract specific components of a date or timestamp value with ease, and perform various calculations or comparisons using those extracted values.