Operating System - HP-UX
1748109 Members
4690 Online
108758 Solutions
New Discussion юеВ

Re: date problem with sqlldr

 
SOLVED
Go to solution
jane zhang
Regular Advisor

date problem with sqlldr

Hi
I have data date.dat file including the following data
20011025
20011025
20011025
20011025

I need to loaded into oracle table ABC which has a create_date column of type date.

The control file I use is like
load data
infile '/home/janez/date.dat'
append
into table ABC
fields terminated by " "
(CREATION_DATE )

It won't work since the sysdate data format is 25-OCT-01. I can not reset nls-date-format parameter since other users are using this format to import data.
Can anybody give me some suggestion, or maybe we can do something about the control file?

Thank you for the help,
6 REPLIES 6
Andreas D. Skjervold
Honored Contributor

Re: date problem with sqlldr

Hi
Here is what to do:
load data
infile '/home/janez/date.dat'
append
into table ABC
fields terminated by " "
(CREATION_DATE ???yyyy-mm-dd???)

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Andreas D. Skjervold
Honored Contributor

Re: date problem with sqlldr

Ooops couldn't get it right...

load data
infile '/home/janez/date.dat'
append
into table ABC
fields terminated by " "
(CREATION_DATE DATE ???yyyy-mm-dd???)

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Andreas D. Skjervold
Honored Contributor
Solution

Re: date problem with sqlldr

This is almost embarrasing;

The correct datemask is "YYYYMMDD" not "yyyy-mm-dd" is I typed: That would be for 2001-10-25 dates...

Andreas

Finaly got it right!
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
jane zhang
Regular Advisor

Re: date problem with sqlldr

Andreas,
Thanks for the solution, it works!
May I ask you a further question? How Can I import the data without the header column?
for example,
the data file looks like,
DATE
20011025
20011025
20011025
20011025
The first line will give error since DATE is not a date value.
I tried to "skip 1" in the control file, but it did like it either.
Regards,
Jane


jane zhang
Regular Advisor

Re: date problem with sqlldr

Hi,
I used skip=1 as command line parameter, sqlldr skipped the first header line.
thanks,
Jane
Andreas D. Skjervold
Honored Contributor

Re: date problem with sqlldr

Hi

I usually depend on sqlloader to skip the header row itself due to data inconsistency, but ofcourse your way is better.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!