Re: oracle date

From: Randall J. Parr <RParr_at_TemporalArts.COM>
Date: Mon, 09 Aug 1999 17:21:24 -0500
Message-ID: <37AF5464.F935D796@TemporalArts.COM>

Thanks for the reply.

My program is reading a variety of delimited text files (ie "unload files"). Each file specifies the table name and column names but does NOT specify the data type. For example:

---

table|country|;

columns|countrycd|descrip|modon|;
data|us|United States|07/02/1999|;
data|de|Denmark|07/01/1999 00:00:00|;
------------------------

Some of this data is dumped from Oracle databases but some comes from other sources. Some "date" fields are just dates and some are data/times.

My program builds an insert statment (which doesn't care about the column type) like

insert into table (countrycd, descrip, modon ) values ( 'us', 'United States', '07/02/1999' );

This works fine except when the date format does not precisely match the NLS_DATE_FORMAT format string. I am trying to find an Oracle setting (or whatever) that allows the time portion of a date string to be optional. It boils down to an NLS_DATE_FORMAT or other setting which allows specifying an optiona part for the default format which governs TO_CHAR( date ).

So far, I haven't found anything. My only choices, currently, are 1) to force all unloaded data (even if coming from a plain date) to include a time portion or 2) add datatype specification in the unloaded data (thus forcing a db independent program to become very database vendor specific).

Thanks again,
R.Parr

"C. Ferguson" wrote:

> Hi Randall,
> since you say your data file contains two fields which doesn't have an associated
> data type,
> programmatically, you should put the second column into a Java date/calendar type to
> ensure
> that it really is a date. You would use the DateFormat class to accomplish this.
> Since your second string will sometimes contain a time portion and
> sometimes not, this will solve your formatting problem. You will use the to_date
> call to insert, but you will
> always know your date format because whatever you threw into the the java date field
> will have
> either initialized the time portion, or it will default it to 0 hours, 0 minutes, 0
> seconds, so when
> you pull it back out of the java date type, pull it out it with the hour, minutes,
> seconds.
> This will solve your problem from a java point of view.
> Hope this helps,
> Cindy
>
> "Randall J. Parr" wrote:
>
> > Thomas Kyte wrote:
> >
> > > ,,,,,,,,,,,,,,,,,,,,,
> >
> > Any suggestions on how to set the NLS_DATE_FORMAT or otherwise fix the following
> > problem?
> >
> > I am trying to insert records with dates which may or may not include
> > the time portion of the date. I am reading these records from a text
> > file using Java and performing the inserts with JDBC.
> >
> > For exmple A) '07/02/1990' or B) '07/02/1999 00:00:00'
> >
> > With NLS_DATE_FORMAT set to 'MM/DD/RRRR' date B gives an error (more or
> > less) "ran out of date format before finished".
> >
> > With NLS_DATE_FORMAT set to 'MM/DD/RRRR HH24:MI:SS' date A gives and
> > error (more or less) "incomplete date"
> >
> > Is there a way to set a date format (preferably a default format) such
> > that the time portion of the date is optional?
> >
> > I am trying to avoid solutions which include wrapping the date to be
> > inserted in JDBC escapes and/or TO_DATE( mydate, 'myformat') because I
> > only have column name and column data in the text file (that is, the
> > text file doesn't contain any information about the column types).
> >
> > Thanks
> > R.Parr
> > Temporal Arts
Received on Mon Aug 09 1999 - 17:21:24 CDT