Re: Time conversions

I'm with Jim on making sure the users enter the data in the correct format, or at least adding a spot of front end functionality to the make some reasonable guesses, e.g.:

You entered 00:91:31. This is incorrect. Did you mean:

(a) 01:31:31
(b) 00:19:31
(c) something else?

Press a,b or c to continue :

• then the program would insert a or b, or re-prompt for the data for c. (to my mind this is more user friendly than just rejecting the data outright)

Not sure why you'd want to do this without the instantly obvious choice of functions (homework assignment?). Whatever language I'd use, I'd split it into substrings then parse them looking for an overflow, modding the value then adding the appropriate value to the field to the left.

Dave.

rj wrote:
> 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.