Using RTRIM with TO_DATE/TO_CHAR PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Using RTRIM with TO_DATE/TO_CHAR PL/SQL

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    NEW YORK
    Posts
    17

    Angry

    I'm a new student, and dont know if its ok to be posting here or not, so tell me right off what/where is appropriate for me.
    That being said, I'm trying to get rid of the trailing spaces that I get when do a TO_CHAR in a PL/SQL program. Here it is for all to check out and if you can get back to me with the correct way to go about it: DECLARE
    state varchar2 (20);
    statehooddate DATE;
    BEGIN
    state := 'Wyoming';
    statehooddate := TO_DATE('07/10/1890', 'MM/DD/YYYY');
    statehooddate := RTRIM(statehooddate);
    DBMS_OUTPUT.PUT_LINE(state || ' became a state on ' ||
    TO_CHAR(statehooddate, 'Month DD, YYYY')||'.');
    END;

    Wyoming became a state on July 10, 1890. As you can see the padded area is after "July", and I want to know how to remove them. I've toyed with Instr and Substr with no luck either, but I'm sure somebody here can help me with this.

    THANKS!!!!!!

  2. #2
    Join Date
    May 2001
    Posts
    11
    Hi
    try this one
    Format Modifier : 'fm'
    if FM is omitted, the month is blank-padded to nine characters
    You have to specify fm if u don't want any blank spaces in the
    to_char() funtion

    BEGIN
    state := 'Wyoming';
    statehooddate := TO_DATE('07/10/1890', 'MM/DD/YYYY');
    statehooddate := RTRIM(statehooddate);
    DBMS_OUTPUT.PUT_LINE(state || ' became a state on ' ||
    TO_CHAR(statehooddate, 'fmMonth DD, YYYY')||'.');
    END;

    Good luck
    Sarath

  3. #3
    Join Date
    Jul 2000
    Location
    india
    Posts
    213

    no diff

    Hi sarath,

    i do not see any difference.....see both the queries...


    SQL> select to_char(sysdate,'fmMonth DD, YYYY') from dual;

    TO_CHAR(SYSDATE,'F
    ------------------
    September 24, 2001

    SQL> select to_char(sysdate,'Month DD, YYYY') from dual;

    TO_CHAR(SYSDATE,'M
    ------------------
    September 24, 2001


    Insead i used(remove the space in the format)...see the follo queries

    SQL> select to_char(sysdate,'fmMonthDD, YYYY') from dual;

    TO_CHAR(SYSDATE,'
    -----------------
    September24, 2001

    SQL> select to_char(sysdate,'MonthDD, YYYY') from dual;

    TO_CHAR(SYSDATE,'
    -----------------
    September24, 2001

    I hope this is what u require....if i am wrong pls correct me..
    Thanks
    pras

  4. #4
    Join Date
    May 2001
    Posts
    11

    Thumbs up

    Hi pras,
    U r wrong u r thinking in only one direction that is for a predefined date , but here the problem is something different he is explicitly converting to date and then using to_Char()
    Your statement will work only when we take a date field or sysdate but here the scenario is different
    test this one

    SARATH=> select to_char(to_date '22/aug/01','dd/mon/yy'),'Month DD, YYYY') from
    dual;

    TO_CHAR(TO_DATE('2
    ------------------
    August 22, 2001

    when u form a date by using to_date function and then using the to_char() function the spaces will come.to avoid that one
    we have to use 'fm' mode in format modes of dates while using to_char function.

    as u said removing the spaces in the format will not solve the problem test this statement

    SARATH=>*select to_char(to_date('22/aug/01','dd/mon/yy'),'MonthDD, YYYY') from dual;


    TO_CHAR(TO_DATE('
    -----------------
    August 22, 2001



    and finally test this one
    SARATH=>*select to_char(to_date('22/aug/01','dd/mon/yy'),'fmMonth DD, YYYY') from dual;


    TO_CHAR(TO_DATE('2
    ------------------
    August 22, 2001


    so finally ur statement will not work for all scenarios
    Thanks
    Sarath



  5. #5
    Join Date
    Jul 2000
    Location
    india
    Posts
    213

    thanks

    Hi Sarath
    Yeah ur right....
    Thanks a lot for clearing my doubt....

    Have a nice time
    pras


  6. #6
    Join Date
    May 2001
    Posts
    11

    Cool

    Hi Pras,
    No Probs Thanks and have a nice time

    Sarath

  7. #7
    Join Date
    Sep 2001
    Location
    NEW YORK
    Posts
    17

    Thumbs up Works great!

    Sarath!

    Thank you so much for your help. The 'fm' does the trick perfectly for me!

    Have a good one!

    Netrix.

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