If you’ve ever used the TO_CHAR() function to format a date that uses the month name, you might have noticed that sometimes the month is returned with right padding.

This happens by default when the month name is shorter than the longest month name, based on the language and calendar in use.

For example, if your language is English and the calendar is Gregorian, the longest month name is September, which is nine characters long. If you return the month from a date, and it’s say, March (five characters long), then by default, March will have four trailing spaces (5 + 4 = 9).

Fortunately, it’s easy to remove this right padding if required. To remove the right padding, use the fm format modifier.

The Problem

First, here’s the problem we’re trying to solve:

SELECT 
    TO_CHAR(date '2030-03-08', 'DD-MONTH-YYYY')
FROM DUAL;

Result:

08-MARCH    -2030

We can see that there’s a large gap between the month name and the year. If we don’t want this gap, we can use the fm modifier, as shown below.

The Solution

Now here’s the solution:

SELECT 
    TO_CHAR(date '2030-03-08', 'fmDD-MONTH-YYYY')
FROM DUAL;

Result:

8-MARCH-2030

Now there’s no gap between the month and the year.

All I did was prepend the format model with fm.

The fm format modifier stands for “Fill Mode”. By default Oracle uses trailing blank characters and leading zeros to fill format elements to a constant width. The width is equal to the display width of the largest element for the relevant format model. Using the fm modifier suppresses all such padding.

You might’ve noticed that the fm modifier also suppressed the leading zero on the day number. If we don’t want this, we can place the fm modifier in front of the MONTH format element instead.

Like this:

SELECT 
    TO_CHAR(date '2030-03-08', 'DD-fmMONTH-YYYY')
FROM DUAL;

Result:

08-MARCH-2030

This time, the fm wasn’t applied to the day, but it was applied to the month and the year.

If we don’t want to suppress leading zeros from the year, then we need to add another fm before the year.

Here’s an example that illustrates what I mean:

SELECT 
    TO_CHAR(date '0030-03-08', 'DD-fmMONTH-YYYY') AS "r1",
    TO_CHAR(date '0030-03-08', 'DD-fmMONTH-fmYYYY') AS "r2"
FROM DUAL;

Result:

            r1               r2 
______________ ________________ 
08-MARCH-30    08-MARCH-0030   

The first column uses just one fm (in front of MONTH).

The second column uses two fm modifiers (one in front of MONTH, and one in front of YYYY).