"rj" <rjeena_at_gmail.com> wrote in message
news:1156153259.035202.178130_at_p79g2000cwp.googlegroups.com...
> Hey Dave,
>
> Thanx for the reply..
> you understand correctly, these values are entered by capturers who
> enter 90 mins instead of 01:30; and 26 hours instead of 1 day 2 hours
> etc..
> Any idea how i can change these without having to use substring and
> div/mod functions?
>
> Dave wrote:
> > rj wrote:
> > > Hi all,
> > >
> > > I have a temp table with data to upload to the real table.
> > > In this temp table there are time values stored as varchar, but are in
> > > the format dd hh:mm:ss.
> > > The problem in some of the values are e.g 00 00:91:31; 00 00:81:57
> > > etc..
> > > When i try uploading this to the real table with data type interval
day
> > > (2) second (6) I get an error saying that Minutes should be between 0
> > > and 59.
> > >
> > >
> > > How do I convert these values to that they will fix the
> > > days/hours/minutes before updating the real table?
> > >
> > > Thanks in advance..
> > >
> >
> > Only you know what 00:91:31 really means (it's a fair guess that it
> > means 01:31:31 but that's still only a guess; another possibility is
> > that this is a field entered by a human and that this is a simple digit
> > transposition error and the correct value is 00:19:31) so one way is to
> > fix invalid dates in the original varchar2 field then your conversion
> > will work without error. Minutes *should* be between 0 and 59 so the
> > error is correct.
>
Get them to enter the correct data? I would load the data in the temp table
as separate fields and calculate the date from there into the real table.
It would make it easier to run error checks before hand.
Jim
Received on Mon Aug 21 2006 - 07:01:09 CDT
