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

    Unanswered: Convert Text to Time

    Dear all,

    I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

    Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

    If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

    Thank you

    George

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL Server is pretty good about implicitly converting text to time. If the time for is really odd, then you may need to use the CONVERT function or create a custom function.

    Can you do this for all 30 tables in a single query? MAYBE using a union query, but don't count on it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Posts
    12
    I am not using SQL Server but Microsoft Access.

    I time had an odd format but i have managed to manipulate it to the format 00:00:00.

    I only need to convert the field from text to date/time. I have tried convert() and CDate() but i cannot make it to work.

    My table is named "01" and the field "Duration".

    Can anyone provide me with the full code to make the change.

    I will use "Macros" and "RunSQLQuery" to make the converion for all tables.

    This is the last problem i have to solve in order to make it work. I have been working on this database for the last week.

    Please note that i am new in SQL with MS Access. I have started only one moth ago

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would suggest that you post this question in the MS-Access forum. They have more experience with the Access GUI and might be able to give you better suggestions.

    I tried using Cdate("13:23:45") in an Access query, and it worked nicely for me. I suspect that that is the conversion that you need, but I'm not certain about what code you need to derive a properly formatted time string.

    -PatP

  5. #5
    Join Date
    Aug 2004
    Posts
    12
    Ok, I thought of something else.

    There is no need to change the data type of the table. I just created a query with all the fields of the table but for Duration i put: CDate([Duration]). The query returns the results as Date/Time and from there i can perform the calculations i require.

    It works.

    Thanks again, problem solved.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Gee, you just have to love it when you inadvertanly solve a problem!

    -PatP

Posting Permissions

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