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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: Problem with Find
    ... Dave wrote: ... Maybe the sum isn't really what you see in the cell--maybe it's formatted ... If the sum adds up to 7.99999, but the cell shows 8 because of formatting ... Then try clearing the Find format. ...
    (microsoft.public.excel.misc)
  • Re: VBA & User Form modules vanish
    ... Dave thanks for the replies - my IT guys have just suggested zipping ... As to the .xlb stuff - thanks ... Tom Ogilvy wrote: ... This format does not support Userforms or sheet module code and this ...
    (microsoft.public.excel.programming)
  • Re: Cell Format Issue
    ... "Dave Peterson" escreveu: ... excel treats it as a formatting character, ... If you try to format that as a date, then excel will do what you say. ... Or you could use another cell with a formula that converts it to a date. ...
    (microsoft.public.excel.misc)
  • Re: custom format & saving
    ... Dave, I tried your approach of using the Change Event and it worked, although ... Format for non-numbers. ... > You can create a workbook template and save it to your XLStart folder (save it ...
    (microsoft.public.excel.misc)