How do I convert '03-apr-2001' to '3-apr-2001'
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    4

    Angry

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

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    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;

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    I Stand corrected.

  5. #5
    Join Date
    Mar 2001
    Posts
    4

    Smile

    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
  •  


Click Here to Expand Forum to Full Width