Date format in sqlldr

[naushi]

I have a file that was bcp'd out of SQL Server. I am trying to load it into an Oracle table and getting an error on date format. The date column is coming in as follows from SQL Server which I have no control over.

1994-03-28 10:57:27.000

I have used the following formats in my control file for the date column and they all error out.

receive_dt date(19) YYYY-MM-DD HH24:MI:SS
receive_dt date(23) YYYY-MM-DD HH24:MI:SSSSS
receive_dt date(23) YYYY-MM-DD HH24:MI:SS.SSS
receive_dt date YYYY-MM-DD HH24:MI:SS
receive_dt date

none of the above works. The errors I get are
record rejected - format code appears twice

or

record rejected - input string ended before converting the date

I gave up - dont know how to get this date to accept.

Any clue will be appreciated.







Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[rcurva]

How about this one:

receive_dt date "TO_DATE(SUBSTRreceive_dt,1,19),'YYYY-MM-DD HH24:MI:SS')"



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[naushi]

Robbie,

Thanks so much for the help. It worked. Now I am getting the following error on each record. finds_repair_order is the table name - any clue?

Record 1: Rejected - Error on table ECLIPSE.FINDS_REPAIR_ORDER.
ORA-01008: not all variables bound

This Error has no action suggested in the book.

BTW my son's name is Robbie too


Regards,
Naushi Hussain
Naushi.hussain@honeywell.com