Re: Time conversions

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

A check constraint might be appropriate here.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org