[SOLVED] How to remove leading zeroes from day and month values in Oracle, when parsing to string using to

How to remove leading zeroes from day and month values in Oracle, when parsing to string using to_char function?

I want to retrieve a date without the leading zeroes in front of the day and month values in a select statement. If I execute the following query

 select to_char(sysdate, 'dd.mm.yyyy') from dual;

I will get 21.03.2014 as a result. Moreover, if today was, for example, 7th of March, 2014, I would get 07.03.2014. How can I get rid of these leading zeroes?

Solution

select   to_char(sysdate,'DD.MM.YYYY') -- Without Fill Mode
,        to_char(sysdate-20,'fmDD.MM.YYYY')  -- With Fill Mode, 20 days ago
  from dual;

Returns:

21.03.2014 | 1.3.2014

Use the fill mode modifier FM once in the expression to suppress leading zeroes and right-padded spaces. Note that FM is a toggle, and does not only apply to the next format element. This means that if you use FM multiple times in one expression, it will re-enable fill mode for all subsequent format elements.

2025-11-02 edit note: Ignore the comments, they address an issue with a previous version of this answer that incorrectly used FM twice.