Re: sql loader time field only no date

rgants1_at_aol.com (Bob) wrote in message news:<ee9df281.0405030513.1d6a89f0_at_posting.google.com>...
> 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

Ok folks thanks for your help. Following is what I managed to figure out.
Two things seem to work fine. One as suggested was why not combine the fields date and time into one.

The key here is that with the timestamp datatype you need to use fractions of time hence the .FF NOT .d

The sql loader portion would look like this

startDate timestamp "MM/DD/YYYY HH24.MI.SS.FF",

or keep them seperate fields and have the following

startDate timestamp "MM/DD/YYYY",
startTime timestamp "HH24.MI.SS.FF",

the later is what I wanted. A default date is added to the startTime. Please keep in mind I am using Oracle 9i here.

thanks again Received on Wed Jun 02 2004 - 07:05:21 CDT