Re: SQL*Loader

I don't have my manual handy, but I think the easiest way would be to tell SQL*Loader to ignore the fractional seconds. Just modify the control file to tell it the field does not include that data: ie instead of
Jun-18-1998 12:00:00:000AM
have it convert
Jun-18-1998 12:00:00

But since the AM/PM part may be needed, you may need to trim out the fractional seconds with SQL functions. Some books don't document this feature (eg, ORACLE: The Complete Reference). There is a way,

Worst case: load it into a text column then convert it using the TO_DATE function something like this:

 to_date(substr(date_string,1,20)||substr(date_string,25,2), 'mon-dd-yyyy hh:mm:ssAM')

The first substring gets the beginning of the time and the second gets the AM/PM,

the double pipe (||) concatenates these back together, and I hope I have the date conversion string right.

Do this as you move the data from the
temporary load table to the real table.

stephen_huxley_at_standardlife.com wrote:

> I'm attempting to load an Oracle db with data from a Sybase database and
> looking for a way to persuade SQL Load that Sybase dates such as :
> Jun-18-1998 12:00:00:000AM are really dates. As far as i can see it doesn't
> like the hundredths and thousandths of a second parts of the date but i don't
> see a way of discarding this.
>
> Can you help ?
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Dec 04 1998 - 15:51:27 CST