Trouble with Oracle SQL Loader and a date field

Question

I have a csv file that is pipe delimited and I'm trying to use SQL Loader to import the data. The data type in the table is Date. I'd like to import just the MM/DD/YYYY but I'm having errors.

My control file code for this field is:

field_a char(1024),
field_in_question DATE'MM/DD/RRRR',
field_c,

Dates in Sample File:

5/28/2019 0:00
3/30/2020 0:00
12/16/2019 0:00

The error I'm currently receiving is:

ORA-01858: a non-numeric character was found where a numeric was expected

Any help would be greatly appreciated.

Answer 1

An oracle DATE type includes a time component. Your input data also has a time component. So just adjust your input date mask to account for it.

field_in_question DATE'MM/DD/YYYY hh:mi'

Notice I've also changed your mask for 'years' to 'YYYY'. The 'RR' and "RRRR' construct was meant as a temporary band-aid to buy time in solving the Y2K bug. And that was twenty years ago. Long past time to no longer need temporary fixes.

Answer 2

Here's how.

Sample table:

SQL> create table test (name varchar2(10), datum date, colc number);

Table created.

Control file (sample data included):

load data 
infile *
replace
into table test
fields terminated by '|'
trailing nullcols
( 
name,
datum "to_date(:datum, 'mm/dd/yyyy hh24:mi')",
colc
)

begindata
Little|5/28/2019 0:00|1
Foot|3/30/2020 0:00|2
Bigfoot|12/16/2019 0:00|3

Loading session and the result:

SQL> $sqlldr scott/tiger control=test23.ctl log=test23.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pon Stu 16 22:35:58 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * from test;

NAME       DATUM                     COLC
---------- ------------------- ----------
Little     28.05.2019 00:00:00          1
Foot       30.03.2020 00:00:00          2
Bigfoot    16.12.2019 00:00:00          3

SQL>