Oracle sqlldr timestamp format headache

Question

Hi - I'm struggling to get sqlldr to import a csv data file into my table, specifically with the field that is a timestamp.

The data in my csv file is in this format:

16-NOV-09 01.57.48.001000 PM

I've tried all manner of combinations in my control file and am going around in circles. I can't find anything online - not even the Oracle reference page that details what all the date/timestamp format strings are.

Does anyone know where this reference page is, or what format string I should be using in my control file for this timestamp format.

For reference, this is what I've most recently tried:

load data
infile 'kev.csv'
into table page_hits
fields terminated by "~" 
( ...
  event_timestamp TIMESTAMP "dd-mmm-yy hh24.mi.ss", 
...)

Thanks in advance.

Answer 1

Hi Kevin,

you can try this format:

event_timestamp TIMESTAMP "dd-MON-yy hh.mi.ss.ff6 PM"

You can browse all available formats in the SQL reference documentation.