Re: Help with query in forms4.5

From: Keith Jamieson <pdkj02_at_email.mot.com>
Date: 1998/03/18
Message-ID: <350F7CB3.72F94D53@email.mot.com>#1/1

As far as I am aware. Oracle does not support date arithmetic such as you have tried. You need to use the oracle date and conversion functions.

I have done something similar to what you appear to be trying to do in forms 4.0. My code moves the date back a day each time a button is pressed.

What I did was declare a text item called datetime as format datetime and populated it using $$DBDATETIME$$ for the current date and time.

My trigger is as follows:

Declare julian_date NUMBER;

                               temp_date varchar(12);

Begin
/*** Convert date to julian number format ***/ Select to_char(:datetime,'J')

    into julian_date
    from dual;

/*** Go back by 1 day ***/
julian_date := julian_date - 1;

/*** convert julian_date to datetime ***/

select to_date(to_char(julian_date,'DATE'),'MM/DD/YY')

   into :datetime
  from dual;
end;

If you just want to validate the date try the following:

/** Declare DATE_FIELD as varchar **/
/** Declare JULIAN_DATE as number **/
SELECT to_char(to_date(:DATE_FIELD,'DD-MON-YY'),'J') into :JULIAN_DATE
from dual;

If an invalid date is entered this will result in an oracle error; This will also catch invalid
dates such as 31-FEB.

dwarakv_at_hotmail.com wrote:

> Hi,
> I am building a form using forms4.5 which takes in a date from the user
> and performs a query on two tables and dumps the results into a temporary
> table. I make sure that the date is stored/accepted correctly by printing the
> date after it is entered. I am running into problems when I making a
> comparison with the input date. I get the following error
>
> FRM-40735: When_Button_Pressed trigger raised unhandled exception
> ora-01858(non numeric found where numeric required).
>
> The statement that I have written is
>
> s_date date;
> splus_date date; --splus_date := s_date + 1
>
> Insert into temp_table (
> select column1, column2,....
> from a, b
> where condition 1 and
> a.ex_date >= s_date and a.e_date < splus_date);
>
> I am comparing the date with the input date(s_date,which is also stored in
> date format). I also tried to use the to_date function to make sure that I
> don't leave the input date in some other format but still I end up with this
> error. Can the gurus please help me with this query.
>
> Thanks
> Dwarak
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Wed Mar 18 1998 - 00:00:00 CST