Date format for SQL*Loader

Question

I am trying to import a file of records into a table and am getting

Rejected - Error on table FUNDPRICE_TEST, column FUNDDATE.
ORA-01843: not a valid month

I did some research and read that Oracle by default expects dates formatted yyyymmdd. Clearly this would throw an error when the first day value (the first record in my case) is more than 12.

How can I format the date in my exported csv file from mm/dd/yyyy 00:00:00 to this yyyymmdd format?

This is of course assuming this is my issue. I could be barking up the wrong tree.

Here is the ctl file after trying date formatting:

load data
infile 'commands/FundPriceDataNEW2.txt'
 into table FundPrice
 fields terminated by "," optionally enclosed by '"'          
 ( FUND, FUNDDATE DATE "mm/dd/yyyy HH24:MI:SS", FAV, FUNDVALUE )

Here are a few records from the FundPriceDataNEW2.txt:

16,9/30/1999 0:00:00,"999999",9.64
16,10/31/1999 0:00:00,"999999",10.06
16,11/30/1999 0:00:00,"999999",10.40

the order is fund,funddate,fav,fundvalue. The error is on the date saying the format is invalid.

Solution

FUNDDATE DATE "mm/dd/yyyy HH24:MI:SS"

In your control card (Control file), try adding the above format for your Date column.

Comments

[Maheswaran Ravisankar]

Can you please paste a sample data for this column here!

[Maheswaran Ravisankar]

@ClintL Can you please make it as FUNDDATE DATE "FMmm/dd/yyyy HH24:MI:SS ? I have added FM to the date format, since the month is just 9 and not 09

[Clint L]

MY GOD. That was it...THANK YOU the toad import for the 500k records would have taken 20+ hours. You saved me a whole day.