Oracle Database provides us with the ability to return the month from a date using roman numerals.

For example, if the month is August, its decimal value would be 08 and it’s roman numeral equivalent would be VIII.

The way to do this is to use the RM or rm format element in your format mask. That will format the month using roman numerals in uppercase or lowercase respectively.

Specifying the date format can be done in several places.

Specify Roman Numerals When Calling a Function

Here’s an example of specifying roman numerals when formatting a date with the TO_CHAR(datetime) function:

SELECT TO_CHAR(DATE '2035-08-11', 'DD RM YYYY') FROM DUAL;

Result:

11 VIII 2035

In this case I specified uppercase roman numerals.

To specify lowercase, simply change the RM to rm:

SELECT TO_CHAR(DATE '2035-08-11', 'DD rm YYYY') FROM DUAL;

Result:

11 viii 2035

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

Specify Roman Numerals For the Whole Session

You can alternatively set the date format for your whole session. This can be useful if you need to do a lot of function calls that return the date.

There are three relevant parameters: NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT.

These parameters have default values that are derived from the NLS_TERRITORY parameter (which has a default value that’s operating system-dependent). However, you can update any of these parameters for the current session if required.

Here’s an example of updating these parameters to use a date format that includes roman numerals for the month portion:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD RM YYYY';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD RM YYYY HH12:MI:SSXFF AM';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'DD RM YYYY HH12:MI:SSXFF AM TZR';

Result:

Session altered.


Session altered.


Session altered.

Now, when I call a function that returns a datetime value, I get the month in roman numerals.

Example:

SELECT TO_CHAR(SYSDATE) FROM DUAL;

Result:

10 VIII 2021

Notice that I passed SYSDATE to TO_CHAR().

If I don’t do that, the NLS_DATE_FORMAT parameter doesn’t take effect:

SELECT SYSDATE FROM DUAL;

Result:

2021-08-10 20:04:49

This issue doesn’t exist with other date formats. I only encountered it with roman numerals.

A More Permanent Solution

The above examples are temporary solutions for returning the month in roman numerals. For a more permanent solution, the NLS parameters can be set at the database level, in the initialization parameter file, or in environment variables. See Methods of Setting NLS Parameters and their Priorities for examples of these, as well as their precedence order in relation to each other.