How to load files using SQLLDR with date format as yyyymmddhhmmss?

Question

I need to load a table with a .csv file which contains date "20140825145416".
I have tried using (DT date "yyyymmdd hh24:mm:ss") in my control file.
It throws an error as ORA-01821: date format not recognized

I require the data in table as "MM/DD/YYYY HH:MM:SS".
Sample data : 20140825145416

thanks in advance.

Answer 1

Well, I would be remiss if I did not point out that the correct answer is to never store dates as VARCHAR2 data, but make it a proper DATE column and load it like this:

 DT DATE "YYYYMMDDHH24MISS"

Formatting is done when selecting. It will make your life so much easier if you ever need to use that date in a calculation.

That out of the way, If you have no control over the database and have to store it as a VARCHAR2, first convert to a date, then use to_char to format it before inserting:

DT CHAR "to_char(to_date(:DT, 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY HH24:MI:SS')"

Note 'MI' is used for minutes. You had a typo where you used 'MM' (months) again for minutes.

Answer 2

I know it's already been said in the previous answer, but it's so important, it's worth repeating. Do not store dates as varchars !!

Answer 3

If your DT column is timestamp then this might work

DT CHAR(25) date_format TIMESTAMP mask "yyyymmddhhmiss"

I used something like this in external tables. Maybe this might help https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm and https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8128892010789