converting char to datetime
Posted on 2005-05-17
I'm importing a text file that has been generated from vsam to ms sql by DTS ---working fine.
what i did is i created two tables and actually they are the same but the first table with varchar data types and the other with a specific type just to solve the problem because what i get from the text file is wrong format, so i have a column in the text file contains data for time and the format is like 01200
What i did in the first table is
update table set column = substring(column,2,4);
update table set column = substring(column,1,2)+':'+substring(column,3,2);
and it will be like 12:00
then i transferred the first table to the other table with data type of datatime,the error that appears says" the conversion of a char data type to a datetime type resulted in an out-of-range datetime value",but if the data type of the table2 was varchar it will successfully imports.
My question is can i now which row the error stops on or is there a log file for this DTS because the text file contains around 5000 record, and the important thing is after it will be imported can i make a formula column to subtract this column with another column with same type .
For example 12:00 - 08:00 = 4
Thanks in advance