Oracle EXTRACT

The Oracle EXTRACT() function extracts a specific component (year, month, day, hour, minute, second, etc.,) from a datetime or an interval value.

Syntax

The following illustrates the syntax of the Oracle EXTRACT() function:

EXTRACT(field FROM source)
Code language: SQL (Structured Query Language) (sql)
Oracle EXTRACT

Arguments

The Oracle EXTRACT() function accepts two arguments:

1) field

The field argument specifies the component to be extracted.

2) source

The source argument is a DATE, an INTERVAL, or a TIMESTAMP value from which a field is extracted.

The following table illustrates which fields you can extract from which value type.

Value TypeAvailable Fields
DATEYEAR, MONTH, DAY
INTERVAL YEAR TO MONTHYEAR, MONTH
INTERVAL DAY TO SECONDDAY, HOUR, MINUTE, SECOND
TIMESTAMPYEAR, MONTH, DAY, HOUR, MINUTE, SECOND

Note that the EXTRACT() function will return UNKNOWN if the combination of field and source result in ambiguity.

Return value

The EXTRACT() function returns the value of the field of the source.

Examples

A) Extracting fields from DATE values

You can extract YEAR, MONTH, DAY from a DATE value by using the EXTRACT() function.

The following example extracts the value of the YEAR field from a DATE value.

SELECT EXTRACT( YEAR FROM TO_DATE( '31-Dec-1999 15:30:20 ', 'DD-Mon-YYYY HH24:MI:SS' ) ) YEAR FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

In this example, we used the TO_DATE() function to convert a date literal to a DATE value.

Here is the result

1999
Code language: SQL (Structured Query Language) (sql)

Similarly, you can extract the values of other fields as shown below:

Extracting month from a date:

SELECT EXTRACT( MONTH FROM TO_DATE( '31-Dec-1999 15:30:20 ', 'DD-Mon-YYYY HH24:MI:SS' ) ) MONTH FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

Result

12
Code language: SQL (Structured Query Language) (sql)

Extracting day from a date:

SELECT EXTRACT( DAY FROM TO_DATE( '31-Dec-1999 15:30:20 ', 'DD-Mon-YYYY HH24:MI:SS' ) ) DAY FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

Result

31
Code language: SQL (Structured Query Language) (sql)

To extract values of HOUR, MINUTE, and SECOND fields, you use TO_CHAR() function.

For example, to extract hour, minute, and second of a current system date, you use the following statement:

SELECT TO_CHAR( SYSDATE, 'HH24' ) hour, TO_CHAR( SYSDATE, 'MI' ) minute, TO_CHAR( SYSDATE, 'SS' ) second FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

B) Extracting fields from INTERVAL YEAR TO MONTH values

For the INTERVAL YEAR TO MONTH, you can extract only YEAR and MONTH fields.

Suppose you have the following interval:

INTERVAL '5-2' YEAR TO MONTH
Code language: SQL (Structured Query Language) (sql)

It is 5 years 2 months.

To extract the value of the year field, you use the following statement:

SELECT EXTRACT( YEAR FROM INTERVAL '5-2' YEAR TO MONTH ) FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

The result is

5
Code language: SQL (Structured Query Language) (sql)

The following example extracts the value of the month field from the interval:

SELECT EXTRACT( MONTH FROM INTERVAL '5-2' YEAR TO MONTH ) FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

2
Code language: SQL (Structured Query Language) (sql)

C) Extracting fields from INTERVAL DAY TO SECOND values

For an INTERVAL DAY TO SECOND, you can extract DAY, HOUR, MINUTE, and SECOND as shown in the following example:

Extract day from an interval

SELECT EXTRACT( DAY FROM INTERVAL '5 04:30:20.11' DAY TO SECOND ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result:

5
Code language: SQL (Structured Query Language) (sql)

Extract hour from an interval

SELECT EXTRACT( HOUR FROM INTERVAL '5 04:30:20.11' DAY TO SECOND ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result:

4
Code language: SQL (Structured Query Language) (sql)

Extract minute from an interval

SELECT EXTRACT( MINUTE FROM INTERVAL '5 04:30:20.11' DAY TO SECOND ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result:

30
Code language: SQL (Structured Query Language) (sql)

Extract second from an interval

SELECT EXTRACT( SECOND FROM INTERVAL '5 04:30:20.11' DAY TO SECOND ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result:

20.11
Code language: SQL (Structured Query Language) (sql)

D) Extracting fields from TIMESTAMP values

You can extract YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND from a TIMESTAMP value as shown in the following examples:

Extracting year from a timestamp:

SELECT EXTRACT( YEAR FROM TIMESTAMP '1999-12-31 23:59:59.10' ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result

1999
Code language: SQL (Structured Query Language) (sql)

Extracting month from a timestamp:

SELECT EXTRACT( MONTH FROM TIMESTAMP '1999-12-31 23:59:59.10' ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result

12
Code language: SQL (Structured Query Language) (sql)

Extracting day from a timestamp:

SELECT EXTRACT( DAY FROM TIMESTAMP '1999-12-31 23:59:59.10' ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result

31
Code language: SQL (Structured Query Language) (sql)

Extracting hour from a timestamp:

SELECT EXTRACT( HOUR FROM TIMESTAMP '1999-12-31 23:59:59.10' ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result

23
Code language: SQL (Structured Query Language) (sql)

Extracting minute from a timestamp:

SELECT EXTRACT( MINUTE FROM TIMESTAMP '1999-12-31 23:59:59.10' ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result

59
Code language: SQL (Structured Query Language) (sql)

Extracting second from a timestamp:

SELECT EXTRACT( SECOND FROM TIMESTAMP '1999-12-31 23:59:59.10' ) FROM dual;
Code language: SQL (Structured Query Language) (sql)

Result

59.10
Code language: SQL (Structured Query Language) (sql)

E) Extracting date components from table data

See the following orders table from the sample database:

orders table

The following statement uses the EXTRACT() function to extract the year and month from the order date and return the number of order per month:

SELECT EXTRACT(year FROM order_date) year, EXTRACT(month FROM order_date) month, COUNT(order_id) order_count FROM orders GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date) ORDER BY year DESC, month;
Code language: SQL (Structured Query Language) (sql)

Result

Oracle Extract Example with Table

F) Extracting date from user input

The following statement attempts to extract the year, month, and day from a date that user inputs:

SELECT EXTRACT(YEAR FROM :input_date) year, EXTRACT(MONTH FROM :input_date) month, EXTRACT(DAY FROM :input_date) day FROM dual;
Code language: SQL (Structured Query Language) (sql)

However, Oracle issue an error:

ORA-30076: invalid extract field for extract source

To fix this error, you need to convert the input date to a date value using the TO_DATE function. Suppose that the format of the input date is YYYYMMDD:

SELECT EXTRACT(YEAR FROM TO_DATE(:input_date,'yyyymmdd')) year, EXTRACT(MONTH FROM TO_DATE(:input_date,'yyyymmdd')) month, EXTRACT(DAY FROM TO_DATE(:input_date,'yyyymmdd')) day FROM dual;
Code language: SQL (Structured Query Language) (sql)

And when you enter the input date as 20191126, the output is as follows:

Oracle Extract User Input Date

In this tutorial, you have learned how to use the Oracle EXTRACT() function to extract the value of a specified field of a date-time value.

Was this tutorial helpful?