Remove leading zeros from Oracle date

Question

I want to output the current date in the format:

1-1-2015 

So without the zero's in this example:

SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY') AS today 
FROM dual

Output:

17-06-2015

Answer 1

You can use fm before MM.

Query

SELECT TO_CHAR (SYSDATE, 'DD-fmMM-YYYY') AS today FROM dual;

Output

17-6-2015

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00216

Add:

To have all leading zero's removed:

SELECT TO_CHAR (TO_DATE('01-01-2015', 'DD-MM-YYYY'), 'fmDD-MM-YYYY') AS today FROM dual;

1-1-2015

To remove leading zero from month number:

SELECT TO_CHAR (TO_DATE('01-01-2015', 'DD-MM-YYYY'), 'DD-fmMM-YYYY') AS today FROM dual;

01-1-2015

fm works as a switch. In this example DD is fine, but MM has the leading zero again.

SELECT TO_CHAR (TO_DATE('01-01-2015', 'DD-MM-YYYY'), 'fmDD-fmMM-YYYY') AS today FROM dual;

1-01-2015