In Oracle Database, the TO_CHAR(datetime) allows us to return a datetime value, formatted in a way that we specify.

One of the things we can do is return the month name from a date.

Full Month Name

When it comes to returning the month name from a date, we have the option of getting the full month name or its abbreviated version.

To get the full month name, use the MONTH format element:

SELECT TO_CHAR(DATE '2035-10-03', 'MONTH')
FROM DUAL;

Result:

OCTOBER

Short Month Name

To get the abbreviated month name, use MON:

SELECT TO_CHAR(DATE '2035-10-03', 'MON')
FROM DUAL;

Result:

OCT

Capitalisation

We can use capitalisation to determine the capitalisation of the output:

SELECT 
    TO_CHAR(DATE '2035-10-03', 'Month') AS "Month",
    TO_CHAR(DATE '2035-10-03', 'month') AS "month",
    TO_CHAR(DATE '2035-10-03', 'Mon') AS "Mon",
    TO_CHAR(DATE '2035-10-03', 'mon') AS "mon"
FROM DUAL;

Result:

       Month        month    Mon    mon 
____________ ____________ ______ ______ 
October      october      Oct    oct   

Language

The language of the month name is determined either explicitly with the NLS_DATE_LANGUAGE initialisation parameter or implicitly with the NLS_LANGUAGE initialisation parameter. 

We can explicitly set the value of the NLS_LANGUAGE parameter with the ALTER SESSION statement. When we do this, it also implicitly sets the value of the NLS_DATE_LANGUAGE parameter.

However, setting the NLS_DATE_LANGUAGE parameter does not change the value of the NLS_LANGUAGE parameter. This enables us to specify a different language for format elements that return spelled values, if required.

Here’s an example of setting the NLS_DATE_LANGUAGE parameter to a different language, then getting the month name again:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'GERMAN';
SELECT 
    TO_CHAR(DATE '2037-10-03', 'Month') AS "Full Month Name",
    TO_CHAR(DATE '2037-10-03', 'Mon') AS "Short Month Name"
FROM DUAL;

Result:

   Full Month Name    Short Month Name 
__________________ ___________________ 
Oktober            Okt                

The default language can be overridden at the function level with a third parameter that specifies the language:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'GERMAN';
SELECT 
    TO_CHAR(
        DATE '2037-10-03', 
        'Month', 
        'NLS_DATE_LANGUAGE = Spanish' 
    )
FROM DUAL;

Result:

Octubre

There are many more format elements available for formatting datetime values in Oracle. See List of Datetime Format Elements in Oracle for a full list.