Re: Simple Oracle Questions!

In article <CqzJs4.5Cs_at_gremlin.nrtc.northrop.com> msallwas_at_world.nad.northrop.com (Michael Sallwasser) writes:
>In article <2snmct$cla_at_ucunix.san.uc.edu> pateljb_at_ucunix.san.uc.edu (Jay Patel) writes:
>>Folks,
>>
>>I'm learning Oralce (6.0), so please, excuse my simple questions.
>>
>>1) How'd I subtract Time??
>>
>> I tried :
>>
>> to_char(f1, 'HH24:MM:SS') - to_char(f2, 'HH24:MM:SS')
>>
>> but, it doesn't work.
>>
>select to_number(to_char(f1,'HH24'))*3600+to_number(to_char(f1,'MM'))*60
> +to_number(to_char(f1,'SS')) -
> (to_number(to_char(f2,'HH24'))*3600+to_number(to_char(f2,'MM'))*60
> +to_number(to_char(f2,'SS')))
>
>This will give you the difference in seconds. I have not actually done
>this specific query but ones very similar. I would validate this before
>you go deplying it widely.
>
>By extension, I think you can see how you would include days into the
>equation assuming the same month. There are less straight forward means
>of doing that, too (I think) You can also nest the above expression inside
>of other functions to convert seconds back to hours, minutes and seconds.
>(The proof is left to the reader) :-)

A far more easier way to subtract time would be f1 - f2. The result is given in unit of days, so multiply by 24 to get hours, 1,440 to get minutes or 86,400 to get seconds. Clean and simple.

--
 Alvin W. Law .............................................. Oracle Corporation
 Project Leader, Cost Management System ........... 300 Oracle Parkway, Floor 6
 Manufacturing Applications .......................... Redwood Shores, CA 94065
 Email: alaw_at_us.oracle.com ............ Voice: 415.506.3390 . Fax: 415.506.7299

ORA-03113: end-of-file on communication channel