Converting dates from one format to another using SQL * Loader control file

Question

The data from infile is in the format MM/DD/YYYY , how can I tell the control file about its upload to the database as YYYYMM ?

Answer 1

When you specify the columns in the INFILE declaration, simply specify the format in which the data is stored. Like this

 load data infile 'whatever.csv' into table t23 fields terminated by ',' trailing nullcols ( col_1 integer , col_2 char , col_3 date "MM/DD/YYYY" , col_4 date "MM/DD/YYYY" , col_5 char ) 

Don't worry about the "to" date format. This is for display only. Oracle stores dates in its own internal representation.

Answer 2

Are you trying to load MM / DD / YYYY data into a char / varchar2 field or a date field?

If you are trying to load it into a date field and want to keep the day of the month, APC's answer is correct. You can always just imagine YYYYMM if that is what you want to do.

If you are trying to load it into a date field and want to truncate it to the first day of the month, I think something like this will work:

date_column date "MM/DD/YYYY" "trunc(:date_column, 'mm')"

If you insert char / VARCHAR2 into the column, you could convert it a little differently:

vc2_column char "substr(:vc2_column, 7, 4) || substr(:vc2_column, 1, 2)"