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

GeorgeFirst, 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.|||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|||MSDN is a big help. See the documentation on Convert() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) either online or in your copy of Books Online (which is installed as part of the SQL client tools).

-PatP|||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 (|||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|||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.