-
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;
-
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.
... 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;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
-
Thanx a lot.
I used the LTRIM format. this really helped.
Thanx again.
____________
Manas
Don't Trouble Trouble Unless Trouble Troubles You
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|