Below are two functions that can be used to extract the month from a date in Oracle Database.

The EXTRACT() Function

You can use the EXTRACT(datetime) function to extract various datetime parts from a datetime value. This includes the month.

Here’s an example:

SELECT EXTRACT(MONTH FROM DATE '2030-12-25')
FROM DUAL;

Result:

12

It’s the MONTH keyword that extracts the month part from the date. We can get other date parts by changing it to the relevant keyword. For example, YEAR, DAY, HOUR, MINUTE, etc.

We can also use the function with other datetime values, such as TIMESTAMP, etc.

The TO_CHAR(datetime) Function

We can also use the TO_CHAR(datetime) function as an alternative method to get the month from a date.

This function accepts the datetime or interval value as its first argument, and a format model as its second argument. The function then converts the value to a data type of VARCHAR2 in the specified format.

The format model specifies the format for which to return the datetime/interval value. The format model consists of one or more format elements. This enables us to carefully craft the results to reflect our desired format.

If we only want to return the month, we can use one of the format elements for extracting the month.

To get the month in 01-12 format, use the MM format element:

SELECT TO_CHAR(DATE '2027-08-03', 'MM')
FROM DUAL;

Result:

08

Full Month Name

To get the full name of the month, use MONTH:

SELECT TO_CHAR(DATE '2027-08-03', 'MONTH')
FROM DUAL;

Result:

AUGUST

Short Month Name

To get the short name of the month, use MON:

SELECT TO_CHAR(DATE '2027-08-03', 'MON')
FROM DUAL;

Result:

AUG