Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!wn14feed!worldnet.att.net!208.48.142.85!newsfeed.news2me.com!Spring.edu.tw!news.nctu.edu.tw!feeder.seed.net.tw!attdv1!ip.att.net!frgp!news.boulder.noaa.gov!not-for-mail From: Thomas Gaines Newsgroups: comp.databases.oracle.server Subject: Re: sql loader time field only no date Date: Fri, 30 Apr 2004 16:44:44 -0600 Organization: NOAA Boulder Lines: 51 Message-ID: <4092D6DC.8E9F049@noaa.gov> References: NNTP-Posting-Host: tg2k.ngdc.noaa.gov Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: boulder.noaa.gov 1083365126 14294 192.149.148.31 (30 Apr 2004 22:45:26 GMT) X-Complaints-To: usenet@news.boulder.noaa.gov NNTP-Posting-Date: Fri, 30 Apr 2004 22:45:26 +0000 (UTC) X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U) X-Accept-Language: en Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:260553 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