Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: SQL Statement...

    All,

    The following SQL statement is showing the LENGTH of 'MONDAY' as 9.

    I would like to know the reason behind this ?


    SQL> SELECT to_char(to_date('14-nov-2005','dd-mon-yyyy'),'DAY'),
    2 LENGTH(to_char(to_date('14-nov-2005','dd-mon-yyyy'),'DAY')) DAY_LENGTH,
    3 LENGTH(TRIM(to_char(to_date('14-nov-2005','dd-mon-yyyy'),'DAY'))) TRIM_LENGTH
    4 FROM dual;


    TO_CHAR(T DAY_LENGTH TRIM_LENGTH
    --------- ---------- -----------
    MONDAY 9 6

    SQL>


    Thanks,
    JD

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Yes, that's documented : DAY Name of day, padded with blanks to length of 9 characters..

    The result must be of type CHAR(9), not varchar2(9), that's all.

    Regards,

    RBARAER
    Last edited by RBARAER; 11-16-05 at 08:13.
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Nov 2005
    Posts
    17
    By default the DATE format models pad to the longest value possible. For your example, WEDNESDAY (length of 9) is the longest possible DAY string so all days are returned with spaces padded to 9. It works to same with MONTH as well. To easily remove the padding, just use the FM qualifier in the format mask like so:

    Code:
      
    1  SELECT to_char(to_date('14-nov-2005','dd-mon-yyyy'),'fmDAY'),
    2  LENGTH(to_char(to_date('14-nov-2005','dd-mon-yyyy'),'fmDAY')) DAY_LENGTH,
    3  LENGTH(TRIM(to_char(to_date('14-nov-2005','dd-mon-yyyy'),'fmDAY'))) TRIM_LENGTH
    4* FROM dual
    SQL> /
    
    TO_CHA DAY_LENGTH TRIM_LENGTH
    ------ ---------- -----------
    MONDAY          6           6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •