Results 1 to 7 of 7
  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
    Aug 2004
    Posts
    15
    First, you will need to use the CAST or CONVERT SQL function to make your text a datetime field (assuming you need the seconds in the time otherwise smalldatetime will work also). Then use the DATEPART function to return just the time portion of the field.

  3. #3
    Join Date
    Aug 2004
    Posts
    12
    I am new in SQL and the book i use doesn't offer much help in th convert function.

    Can you give me the sql statement to convert the field duration ofthe table 01 from text to datetime?

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    MSDN is a big help. See the documentation on Convert() either online or in your copy of Books Online (which is installed as part of the SQL client tools).

    -PatP

  5. #5
    Join Date
    Aug 2004
    Posts
    12
    I still can't figure it out.

    I use the following:

    UPDATE 01
    set CONVERT (datetime, Duration);

    and

    UPDATE 01
    set CAST (Duration as datetime);

    I get a syntax error after (

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You'll have to post at least a few lines of code for me to be able to help you. I can't figure out what you want from what you've posted so far.

    -PatP

  7. #7
    Join Date
    Aug 2004
    Posts
    12
    My table is named "01".

    One of the fields is "Duration".

    The table is imported from a csv file. The Duration is not in a valid date/time format so i have to import it as "Text", modify it and convert is to "Date/Time".

    I can easily do this from the design view of the table but since i have 31 similar tables, i require a faster way (i don't want to go through all 31 tables to make the changes, it takes too long). The fastest way i can think is through SQl.

    So, it would be much apreciated if someone can provide me with the command to update the date type of "Duration" field from "Text" to "Date/Time" using SQL or a Macro.

    I will use the command to do the same change for all 31 tables.

    This is the last problem i have to solve before i can make the database work.

Posting Permissions

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