Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    34

    Unanswered: LENGTH function returns bad values

    I have a field, MDTIME, that shows raw values as such...
    124306
    75534
    100508
    2502
    134015

    etc.. but when I use LENGTH(MDTIME) I get 6 for every row.

    This made me think the values were acutally as such...
    124306
    075534
    100508
    002502
    134015

    with leading zeros somehow not showing, but actually there in the tables.

    so then I did SUBSTR(MDTIME,1,2) and not once did I get a 0 (zero) as a first character. IS there something wrong with LENGTH? Is it telling me the LENGTH of the field or the string in it? I want the LENGTH of the string in it if that's possible. thanks for any help you can offer.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if the filed is char and must be because substr is being used, all characters are displayed in select try
    select mdtime,length(mdime),hex(mdtime) from ..
    because this is char(6) the length is always 6
    where do you display data in command line or other application. eg excel would remove leading zeroes
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    That is because you have defined the field as CHAR(6). To get variable field length you'll have to define your column as VARCHAR(6) instead.
    IBM Certified Database Associate, DB2 9 for LUW

  4. #4
    Join Date
    Sep 2008
    Posts
    34

    solved

    Thanks for the help! You were right about the field type being CHAR instead of VARCHAR. I ended up using SUBSTR(DIGITS(MDTIME),1,4) to get the time from that field (I don't need the seconds, that's why it's 1,4 instead of 1,6)

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    if using time data why don't you use time data type?
    Regards

Posting Permissions

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