In Oracle Database, we can use the TO_CHAR(datetime) function to return the various date parts from a datetime value, including the short month name.

The short month name is also referred to as the abbreviated month name. Examples of returning the short month name are below.

Example

To get the abbreviated month name, use MON:

SELECT TO_CHAR(DATE '2030-09-25', 'MON')
FROM DUAL;

Result:

SEP

Capitalisation

In the above example I used uppercase MON, which resulted in the short month name being returned in uppercase.

We can change the capitalisation of this argument to Mon in order to return the result in a capitalised format.

SELECT TO_CHAR(DATE '2030-09-25', 'Mon')
FROM DUAL;

Result:

Sep

We can provide it in lowercase (mon) to return the short month name in lowercase:

SELECT TO_CHAR(DATE '2030-09-25', 'mon')
FROM DUAL;

Result:

sep

There are many more format elements available for formatting datetime values in Oracle, and the above format element can be combined with others to produce a longer format model.

Example:

SELECT TO_CHAR(DATE '2030-09-25', 'Dy, dd Mon YYYY')
FROM DUAL;

Result:

Wed, 25 Sep 2030

See List of Datetime Format Elements in Oracle for a full list of format elements that can be used to construct a format model.