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

Question

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?

Answer 1

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

Returns

21.03.14    | 1.3.14 

FM Fill mode.

In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary.

Answer 2

Try this:

select to_char(to_date('20-oct-2000'),'fmmm/dd/fmrr') from dual

The above query will remove the leading zeroes from day and month values in Oracle.