Hi all,
I need some help with loading date formats using sqlldr.
I've researched it on the internet, but not really finding good clear examples that help in my case.
I am trying to load some data from another source into our Oracle database, however, the "date" field includes extra characters that i'm trying to deal with.
For reference, I'm running Oracle 11.2 on Linux 5.7.
First, the actual date input is:
April 18, 2013 8:50:44 AM EDT
I tried to input it a few ways but was unable to load it until I physically removed the "AM EDT" from the input string.
My first question is if there is a way to RTRIM and get rid of those extra characters.
Either that, or I need a way to convert the "AM EDT" into our standard date format.
My controlfile has the following:
LASTCHANGEDAT date "Month dd, YYYY HH MI SS",
This will load the data, but only if I strip out the "AM EDT" from the actual data input which is not feasible.
Also, how would I handle date that comes in as: April 18, 2013 3:50:44 PM EDT
Also, I tried the following, but it gives me a different error:
LASTCHANGEDAT "to_date(:LASTCHANGEDAT,'DD-MON-YYYY HH24:MI:SS')",
gives me error:
Record 1: Rejected - Error on table NTWKREP.INTERFACE, column LASTCHANGEDAT.
ORA-01858: a non-numeric character was found where a numeric was expected
Edited by: 974632 on Apr 22, 2013 12:41 PM