datetime addition/subtraction

Hello all,

Yes, I have yet another question that I'd like to ask the grp help on. First of all, thanks to Carl Pederson and Scott Tiger for suggestions to my previous questions. Both suggested that we save the string containing the last value of :sytem.last_query, and pass this to SQR. We implemented this, doing some modifications to it. It seems that SQR requires the "FROM" clause to be explicit (which makes sense), but the "WHERE" clause could be dynamic. We wrote the SQR script to parse out the parts needed and it works like a charm. Thanks gents.

Now, for my questions. How do I add times to a field?? What I want to do is take a "datetime" field in sqlforms30, add a certain number of hrs to it to generate a new datetime. Here is what has been tried:

0. Dividing the # hrs by 24 and adding this to get the new day (my

   predecessor's method). This causes the form to reject the operation    with a "ORA-1802: Julian Date is out of range" error when the    addition takes place.

1. Changing the variables' types to "datetime" and doing the addition. I later discovered that there is no "datetime" type in PL/SQL (the trigger code) while there is in the form (I hate when things are inconsistent like this).
2. Converting the dates to Julian days from datetime fields, doing the addition then. This results is a "ORA-1830" error, telling me that the fraction part of the variable, which represents the hr:mm portion of the date, got chopped.

Most of our operations involve some kind of hr addition/subtraction to fields, so this is an issue that is vital for us to resolve. I'm sure there's an easier was than taking the datetime field apart piece by piece and processing  it that way. Suggestions??

(The specs: sqlforms30 running Oracle 6 on a Sequent Symmetry (ptx/dynix))

As always, I will summarize answers to the grp.

-pmm

-- 
Paul M. Mickel			  	   Internet:mickel_at_oes.orst.edu
Sysadmin, Oregon Extension Service	   Corvallis, OR 97331
Database Programmer, Teledyne Wah Chang    Albany, OR 
Disclaimer: Neither employer has *ever* claimed any of my opinions.