In Oracle Database, you can use the fm (fill mode) format modifier to suppress any leading zeroes that might be applied to a date. This format modifier suppresses padding, which includes leading zeros and trailing blanks.

To use the fm format modifier, include it in your format model when specifying how dates should be formatted. For example when using the TO_CHAR() function to format dates, or in NLS parameters such as the NLS_DATE_FORMAT parameter when setting the date format for your current session.

Example

Here’s an example to demonstrate:

SELECT 
    TO_CHAR(DATE '2009-08-01', 'DD-MM-YY') AS "Without fm",
    TO_CHAR(DATE '2009-08-01', 'fmDD-MM-YY') AS "With fm"
FROM DUAL;

Result:

   Without fm    With fm 
_____________ __________ 
01-08-09      1-8-9     

We can see that the original result (without the fm modifier) returns leading zeros for all date parts. In this case, the fm modifier suppressed the leading zero on all three date parts.

The fm modifier only affects the result if there are actually leading zeros in the result.

Here’s a date that includes date parts that take up two digits, and therefore don’t have leading zeros:

SELECT 
    TO_CHAR(DATE '2029-12-01', 'DD-MM-YY') AS "Without fm",
    TO_CHAR(DATE '2029-12-01', 'fmDD-MM-YY') AS "With fm"
FROM DUAL;

Result:

   Without fm    With fm 
_____________ __________ 
01-12-29      1-12-29    

How to Specify Fill Mode Only on Some Date Parts

The above example suppressed the leading zero on all date parts. The leading zero was removed from the day, the month, and the year. This is because, when we place a single fm at the start of the format model, it suppresses all subsequent leading zeros in the result.

Suppose we only want to remove the leading zero from the day and month, but retain it on the year. In this case, we can do the following:

SELECT TO_CHAR(DATE '2009-08-01', 'fmDD-MM-fmYY')
FROM DUAL;

Result:

1-8-09

The fm modifier basically works like a switch. When you include one, it suppresses zeros until the end, or until it encounters another fm modifier. If there’s a second fm modifier, then leading zeros are not suppressed beyond that point, and so on.

Here’s what happens when we include three fm modifiers – one before each date part:

SELECT TO_CHAR(DATE '2009-08-01', 'fmDD-fmMM-fmYY')
FROM DUAL;

Result:

1-08-9

So in this case, the fm modifier was basically enabled, then disabled, then enabled again.

NLS Parameters

You can also specify fill mode in your NLS parameters, such as the NLS_DATE_FORMAT parameter when setting the date format for your current session.

Example:

ALTER SESSION SET NLS_DATE_FORMAT = 'fmDD-MM-fmRR';

SELECT DATE '2009-08-07' FROM DUAL;

Result:

7-8-09