Re: sql loader time field only no date

Thomas Gaines <Thomas.Gaines_at_noaa.gov> wrote in message news:<4092D6DC.8E9F049_at_noaa.gov>...
> Bob -
>
> I haven't tried do this sort of load, but perhaps an
> "INTERVAL DAY TO SECOND" datatype for the last
> column would be appropriate. It depends upon your
> version of Oracle whether this type is available to you.
> I know that you're
> not specifying the day in this last column, but perhaps
> there's a way to set the day part to zero.
>
> I have to ask, however, why you're separating the
> last two fields. Don't the two of them, taken
> collectively, represent the start time? So why not
> store them together in a simple date column? Just
> wonderin'...
>
> Thanks,
> Tom
>
> Bob wrote:
>
> > Thanks in advance.
> >
> > I have the following comma seperate fields:
> >
> > dlls001,0x000123456,545678,GMT-4:00-Eastern-DST,04/16/2004,10:18:22.4
> >
> > the problem is with the last field. I need to treat this as a seperate
> > TIME ONLY field. I can not figure out how to account for this in a sql
> > loader control file. I keep failing because the sql loader wants a
> > date part.
> >
> > I am stuck with the following control file:
> >
> > LOAD DATE
> > APPEND INTO TABLE ATTEMPT_REC
> > FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> > (
> > node char,
> > gateway char,
> > accId char,
> > timeZone char,
> > starDate date "MM/DD/YYYY",
> > startTime timestamp "hh:mm:ss:d"
> > )
> >
> > the error is Rejected - Error on table ATTEMPT_REC, column STARTTIME
> > ORA-01843: not a valid month

Tom,

Thanks for the reply I appreciate your time! I must confess what I have shown is not totally complete in that there are about 125 or so fields. I just wanted to show a snap shot of what I was doing. So with respect to having two fields that seemingly could be combined into one I am at the mercy of a telco switch spitting it out this way. You are right it would make more sense.

So I am stuck with a separate date field and then separate time fields in fact startTime and dicsonTime for usage calculations. I thought about filtering through a PERL program and making that field into one but we are talking about millions of records a day. Seems like a worst-case fix.

I was using Sybase and Sybase didn't care, it just added a generic date, appended it to the time. I saw in the SQL ANSI standard a time data field type but it doesn't seem to be supported by my Oracle 9i, which would have done the trick.

I think your right I need to figure out how to zero out the date portion or use a default date? I will have a look at how I might apply INTERVAL DAY TO SECOND Thanks
Bob Received on Mon May 03 2004 - 08:13:47 CDT