Re: Managing DATE in FORMS

Paolo Dall'Olio wrote:
>
> I'm in trouble for the following problem:
>
> 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?

Several ways.  

> I've got the following two solutions ... but I don't like neither:
>
> 1- Do not use DATE field in the database, but only varchar2 (when I'm
> just interested in the day and not in the hour)

I would not recommend this.  

> 2- in the form, manage the date field in the following manner:
>
> - create a DATE field (say nodb_block.ins_date) NOT in-database
> - hide the in-database date field (say emp.ins_date)
> - in the Pre-Query trigger, put the statements:
>
> IF :nodb_block.ins_date IS NOT NULL THEN
>
> Set_Block_Property('block0',DEFAULT_WHERE,'To_Char(ins_date)='''||To_Char(:nodb_block.ins_date)||'''');
> ELSE
> Set_Block_Property('block0',DEFAULT_WHERE,NULL);
> END IF;
Where in the heck did this come from? I would definitely stay away from this.

Yes, Oracle stores the date and time in a date field. A date field is actually 7 bytes which hold the century, year, month, day, hour, minute, second. If you refer to a date as 01/02/97 as you did above you are implicitly specifying midnight (i.e., 01/02/97 00:00:00) for your date. I must also warn you that using two digit years like that is extremely dangerous, especially with the year 2000 coming around the corner. ALWAYS USE YYYY NOT YY for the year.

It is not a problem if you are only concerned about the date portion of a date field. If you ever use sysdate to populate the field, use trunc(sysdate) which will truncate the time and make it midnight (then if you query on 01/02/1997 it will match. If the date field is user entered and you only let them enter the date portion of the field, the database will assume midnight as the time when it inserts the value.

In short, make sure that times are always midnight on your date fields so that you can query them without specifying a time. Keep in mind that the easiest way to get rid of a time on a date field is to use TRUNC(date_field).

Chris Halioris
Tactics, Inc.
hali_at_tacticsus.com Received on Fri Feb 28 1997 - 00:00:00 CET