How do I convert '03-apr-2001' to '3-apr-2001'

How do I convert '03-apr-2001' to '3-apr-2001' ? Please help asap.

Hi,
you can use alter session set NLS_DATE_FORMAT = 'D/MMM/YYYY' , but you'll lose all leading decimal number for dates like 14/03/2001, so what you have to do is convert the date type to varchar2 through PL/SQL and user substr;

ex:
select substr(to_char(sysdate),2) from dual;

Quote:

---

Originally posted by rotem_fo
Hi,
you can use alter session set NLS_DATE_FORMAT = 'D/MMM/YYYY' , but you'll lose all leading decimal number for dates like 14/03/2001

---

Format model 'D' has a different purpose, it has nothing in common with 'DD' . 'D' returns the ordinal number of the day of week (1-7), so it can not be used for what manas wants.

Quote:

---

... so what you have to do is convert the date type to varchar2 through PL/SQL and user substr;
ex:
select substr(to_char(sysdate),2) from dual;

---

This is also no good, as it will cut off every first character from the date, that is not only '0', but also '1', '2', and '3'.

What manas could use are two options:
1. Use a LTRIM() character function:
SELECT LTRIM(TO_CHAR(SYSDATE,'dd-mon-yyyy'),'0') FROM dual;
or
2. Use a special 'FM' format model modifier in a TO_CHAR format model, which suppresses leading zeroes in numbers, like
SELECT TO_CHAR(SYSDATE,'FMdd-mon-yyyy')) FROM dual;

I Stand corrected.

Thanx a lot.
I used the LTRIM format. this really helped.
Thanx again.

____________
Manas