Re: sql*loader question

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Thu, 21 Oct 1999 16:27:37 -0700
Message-ID: <7uo7me$qmo$1@plo.sierra.com>

Oracle has no time elements that look like nn.nn.nnnnnn

With the sample you have given, the MOST that you can do is

1.create a temporary import table such that the date field is VARCHAR2(..)
2.SQL*Load your data.
3.Use a query involving TO_DATE and SUBSTR to convert the DATE information

and store it in the permanent table
i.e. TO_DATE(SUBSTR(your_date_field,1,10),'YYYY-MM-DD')

Oracle's best at date/time fields is (generally) of the form DD-MON-YYYY HH24:MI:SS
where HH24 is military time.

If, in your example, 23.23.023423 means hour 23, minute 23, and 023423 seconds since, say, Tuesday, or midnight, etc.,
then the above query could be changed to salvage some of the time, i.e. TO_DATE(SUBSTR(your_date_field,1,16),'YYYY-MM-DD.HH.MI') and you could do some arithmetic on the SUBSTR of the remaining seconds to compute a "reasonable"
"seconds" value.

<raju_pillai_at_yahoo.com> wrote in message news:7uhmc8$5l8$1_at_nnrp1.deja.com...
> I am trying to load a timestamp field from db2 to a date field in
> oracle. This input file has been exported from a db2 database using
> export delimited option. The exported file looks like this :
> 1,test,"1998-03-04-01.23.23.023423","test data"
>
> the third field is a timestamp field, when I try to load this into
> oracle it does'nt like it. In oracle that field is defined as a date
> field. How can I load this field into oracle. I tried to use date(26)
> and giving the picture, it did'nt work. Please help.
>
> Thanks
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Oct 21 1999 - 18:27:37 CDT