How do I convert '03-apr-2001' to '3-apr-2001' ? Please help asap.
Printable View
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;
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:
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
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'.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;
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