Convert date from one format to another using SQL*

Question

The data from the infile is in the format MM/DD/YYYY how do I tell the control file to load it into the database as YYYYMM?

Answer 1

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

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

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

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

If inserting into a CHAR/VARCHAR2 column, you'd could to transform it a little differently:

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

Answer 2

When you specify the columns in the INFILE declare just identify the format the data is held in. 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. That is only for display purposes. Oracle stores dates in its own internal representation.