Printable View
Hello,
I am converting the data from sqlserver into flat file. I am using sqlloader to load the data into oracle9i. The date format in flat file is 2005-04-11 00:00:00.000(it came from sqlserver). What type of date format i can specify in sqlloader control file to load this data.
I would appreciate if any one could help me on this. thanks.
you can load in first to oracle table as varchar2(23) then add another column (col2 date) and update this
column as update t1 set col2=to_date(substr(col1,1,19),'yyyy-mm-dd hh24:mi:ss')
then alter table t1 drop column column col1;
Or
in the sqlserver side to can use the convert function to char
and use substring to trim the milliseconds.
I am Assuming the milliseconds can be discarded here.
Dear yxez, thank you. it is also good idea. I can do this. If any one has any other idea's, please provide. thank you so much.
You can specify a SubStr() to trim the fractions and a to_date() on the input in the sql*loader control file.
I like that...show sample code pls
Thanks davey...
I have this very problematic text file too converted from our MS-sqlserver. The text file contains some rows which are wrapping to the next line. This is usually caused when u allowed to enter wrapped paragraph in the column like REMARKS or ADDRESSES. the
record will split into 2 lines where it encounter the ENTER character.
And also the same with the sepator like optionally enclosed by '"'
some of the ADDRESS colums contains the '"' within thus creating
error like ."McDonald"s Residence",....,
Can you give hint how to handle this problem?
Thanks