Re: Managing DATE in FORMS

In article <33147016.B8B_at_kjws.com>,
Paolo Dall'Olio <pdallolio_at_kjws.com> wrote:
>
> In a Form (dev/2000 1.3 x win95) I have a DATE field corrisponding to
>a database DATE field ... when I try to make a query on this field (e.g.
>01/02/97), no record is returned (NLS_DATE_FORMAT is set correctly, both
>on server and client).
> (Similarly in PL/SQL I get nothing if I do: SELECT * from EMP where
>ins_date='01/02/97';)
>
>
>I was told from hot line service the problem is the following:
>a database date value contains (of course) not only the date but also
>the hour ... so when I try to inquery just by date, I get nothing.
>
>First question: is it true?

YES.
>Second problem: how to resolve it?

If you are using Enter Query mode, don't enter the date in the date field. Instead, put a colon (:) in it and press Execute Query. The forms Query/Where window will pop up. In the window, type:   TRUNC(ins_date)=01/02/97
or better yet, type
  TRUNC(ins_date)=to_date('01021997','MMDDYYYY') and click OK or press F10 (accept).

If you are trying to automate it for your users, then your second solution is close to the best way. I might change it to this: Use your original date field (you might want to change its type from date to datetime, by the way).

In the Pre-Query trigger, use the statements:

  IF :block0.ins_date IS NOT NULL THEN
    Set_Block_Property('block0',DEFAULT_WHERE,'TRUNC(ins_date)=to_date('''

       ||to_char(:block0.ins_date,'YYYYMMDD')||'''YYYYMMDD''');

I hope this helps.

Steve Cosner Received on Fri Feb 28 1997 - 00:00:00 CET