#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    5
    Rep Power
    0

    Help cahanging existind data in a time field


    I have an existing database/table and some records in the time field or H:M:SS format while others are HH:MM:SS format. I have corrected the issue causing this however need to convert all to H:M:SS (6:1:28) to HH:MM:SS(06:01:28)

    Table name = TABLE1,TABLE2,TABLE3 (they all have a time field and all need to get converted)

    Field name = TIME
    Any Suggestions
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by rmontz
    I have an existing database/table and some records in the time field or H:M:SS format while others are HH:MM:SS format. I have corrected the issue causing this however need to convert all to H:M:SS (6:1:28) to HH:MM:SS(06:01:28)

    Table name = TABLE1,TABLE2,TABLE3 (they all have a time field and all need to get converted)

    Field name = TIME
    Any Suggestions
    Is the fields' type TIME, or it's char/varchar? I guess that they are char/varchar, am I correct?
    Last edited by mIRCata; April 5th, 2011 at 02:54 AM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    5
    Rep Power
    0
    Originally Posted by mIRCata
    Is the fields' type TIME, or it's char/varchar? I guess that they are char/varchar, am I correct?
    yes it is (var text) varchar(8)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by rmontz
    yes it is (var text) varchar(8)
    And what is the reason for not using type TIME for these fields? It's 4 byte(you'll save space) and you won't have these problems.

    Try this and see does it help you
    Code:
    UPDATE TABLE1 SET 
    FIELD1 = LPAD(SUBSTRING(FIELD1 FROM 1 FOR POSITION(':', FIELD1) - 1), 2, '0') || ':' ||
    LPAD(SUBSTRING(FIELD1 FROM POSITION(':',FIELD1) + 1 FOR POSITION(':', FIELD1, POSITION(':', FIELD1)) - 1), 2,'0') || ':' ||
    LPAD(SUBSTRING(FIEDL1 FROM POSITION(':',FIELD1,POSITION(':', FIELD1) + 1) + 1),2,'0') FROM     TABLE1
    WHERE
    CHAR_LENGTH(FIELD1) < 8

IMN logo majestic logo threadwatch logo seochat tools logo