Oracle sqlldr timestamp format headache

oracle format timestamp sql-loader
Kevin picture Kevin · Dec 30, 2009 · Viewed 35.4k times · Source

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", 
...)

Answer

Vincent Malgrat picture Vincent Malgrat · Dec 30, 2009

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.

Related questions

Oracle SQL - DATE greater than statement

As the title says, I want to find a way to check which of my data sets are past 6 months from SYSDATE via query. SELECT * FROM OrderArchive WHERE OrderDate <= '31 Dec 2014'; I've tried the following but it returns …

Read More
Why do i get #### in the NUMBER column after format... Oracle?

I have two problematic columns: Fee NUMBER type, AdjFee also NUMBER. After column Fee format a5; select Fee ID smth from Visit; i get Fee ID smth #### 123 klkl #### 654 rfjgr

Read More
Oracle to_date function. Mask needed

I have string of date from xml file of such kind: '2010-09-09T22:33:44.OZ' I need to extract only date and time. I want to ignore symbol T and .OZ (time zone). Which mask I should use? Thanks …

Read More