Re: Managing DATE in FORMS
Date: 1997/02/28
Message-ID: <5f59jg$q5b_at_shadow.CSUFresno.EDU>#1/1
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