string transformation to datetime
Posted on 2003-03-12
I am trying to import information from a text file into a table using a DTS package created through the GUI. I am experiencing difficulties transforming a string representing the time into a datetime field. I have one column representing the date (format is MM/dd/yyyy - e.g., 03/12/2003) and that transforms with no problem. I have another column representing the time in the format of HH:mm:ss (e.g., 13:15:03). The original text file is tab delimited and has several columns but I have simplified things for testing purposes and have created a comma delimited file with just 2 columns: date, and time.
The data in the text file looks like this:
The date converts fine. When creating the transformation I run the test and it says it works fine; however, when I execute the task, the time conversion gives me the following error:
The number of failing rows exceeds the maximum specified.
Insert error, column 2 ('time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
The destination table has both fields as type datetime.
How do you convert a simple time string into a datetime data type?