DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: date format 2005-04-11 00:00:00.000

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    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.

  2. #2
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You can specify a SubStr() to trim the fractions and a to_date() on the input in the sql*loader control file.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    I like that...show sample code pls
    Behind The Success And Failure Of A Man Is A Woman

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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
  •  


Click Here to Expand Forum to Full Width