Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Posts
    12

    Unanswered: Replace data using sql

    I have a database of about 300,000 records.
    The records were imported from a csv file.
    One of the fields is duration.

    The data in duration are like ths:
    1 second: 0:01
    26 minutes: 26:00

    If i put the format of the field as time, the data are messed up.
    0:01 becomes 1 minute.
    26:00 becomes 1 day 2 hours.

    I currently have duration as text.

    How can i use sql or visual basic to replace all the data so that they can have the format "00:00:00"?
    (0:01 becomes 00:00:01, 26:00 becomes 00:26:00)

    I need the duration in time format in order to be able to make sum calculations.

    I will be doing the same calculations every month so i need the above procedure to be able to execute it every time i need to.

    Thank you in advace

    George

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This smells easy. I'd try:
    Code:
    CREATE TABLE tRousoug (
       foo		VARCHAR(8)		NOT NULL
       )
    
    INSERT INTO tRousoug (foo)
       SELECT '0:01' UNION ALL SELECT '26:00'
    
    SELECT Left('00:00:00', 8 - Len(foo)) + foo
       FROM tRousoug
    -PatP

  3. #3
    Join Date
    Aug 2004
    Posts
    12
    My Table is named "June 2004" and the field containing the data "Duration".

    Can you rewrite the code using the above information since i am not able to make it work?

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok.
    Code:
    SELECT Left('00:00:00', 8 - Len(Duration)) + Duration
       FROM [June 2004]
    -PatP

  5. #5
    Join Date
    Aug 2004
    Posts
    12
    Ok. That seems to work. But ut does not replace the data. It only creates a query with the changes. The field data in the table remain the same.

  6. #6
    Join Date
    Jul 2004
    Posts
    13
    Write an UPDATE statement.

    Perhaps you should at least bother to open a book or try to look at the documentation before posting to forums asking people to do your work for you. I'm sorry for being so harsh but it really doesn't seem like you're trying very hard. If I am wrong and you did try with the documentation please accept my apologies.

    Dag

  7. #7
    Join Date
    Jul 2004
    Posts
    3
    dagjo, you sound quite experienced, im currently on some microsoft courses, could you reccommend any good books at all?

  8. #8
    Join Date
    Jul 2004
    Posts
    13
    Oh, and by the way, the solution is

    UPDATE [June 2004]
    SET Duration = Left('00:00:00', 8 - Len(Duration)) + Duration

  9. #9
    Join Date
    Aug 2004
    Posts
    12
    Sorry about this, end of a working day, i'm a little bit tired. I think i need some time off and a revision of my books.

    Thank you

  10. #10
    Join Date
    Jul 2004
    Posts
    13
    Pace,

    sorry, but I'm not primarily a db developer and thus I've got pretty limited knowledge of books and certainly not enough to tell which ones are good and which ones aren't.

    I can and do recommend to use the help files that ship with SQL Server. As a general rule, I recommend trying to look it up in the doc before posting to forums.

    Dag

  11. #11
    Join Date
    Jul 2004
    Posts
    3
    on my querying using transact sql course they were describing how to use the help effectively as I never had a clue when I used it... its surprising just how much help the help is, once you know how to read it properly of course.
    Thanks all the same Dag!

Posting Permissions

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