CODEWITHSUNDEEP
sqloracle-databasedate
Mvz
Mvz

Reputation: 507

Remove leading zeros from Oracle date

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

Upvotes: 5

Views: 8376

Answers (2)

massie
massie

Reputation: 556

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

Upvotes: 8

David Chavez
David Chavez

Reputation: 617

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

FM is fill mode, you can find more information here

Upvotes: 0

Related Questions