-
date format 2005-04-11 00:00:00.000
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.
Last edited by yxez; 11-28-2006 at 08:52 PM.
Behind The Success And Failure Of A Man Is A Woman
-
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
Behind The Success And Failure Of A Man Is A Woman
-
-
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
Last edited by yxez; 11-29-2006 at 03:40 AM.
Behind The Success And Failure Of A Man Is A Woman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|