[comp.databases] Date/Time in Oracle SQL-Forms, how to do it?

chas@sfc.Wichita.NCR.COM (Charles Binford) (06/28/90)

How does one setup a form to handle input/update/display of an
oracle database DATE field in a Form.  All works fine if you don't
mind every date having a time of 12:00 a.m. (00:00:00).

The Designer's reference mentions something having to use to_date and
to_char to convert back and forth between the form and the table.  It
is not very specific.  I have tried several things but have yet to
come up with the magic trick.

Any help would be appreciated (especially if it contains specific
instructions on how to setup the triggers).  Thanks, Charles.

We're using Oracle 5.1.
-- 
Charles Binford, Automation Engineering, NCR PPD Wichita
    <C.Binford@Wichita.NCR.COM>
    <uunet!ncrlnk!ncrwic!c.binford>

tensmekl@ingr.com (Kermit Tensmeyer) (06/30/90)

In article <690@sfc.Wichita.NCR.COM> chas@sfc.Wichita.NCR.COM (Charles Binford) writes:
>How does one setup a form to handle input/update/display of an
>oracle database DATE field in a Form.  All works fine if you don't
>mind every date having a time of 12:00 a.m. (00:00:00).
>
>We're using Oracle 5.1.
	This situation had me stumpted and I called Oracle (actually,
	a consultant who works for Oracle in Bethesda Md.) His explanation
	made sense. Date in the Oracle table sense containg information
	of the form "15-APR-90 18:60:23:400" but the date field that
	forms uses is of the form "15-APR-90". When the field is written
	back to the table after being updated the time is stripped and
	the effected time is set to zero. 
		Solutions

		1) Don't Update the table from the form
		    (Wasn't acceptable to me either)

		2) Set up a post-query trigger on the date screen field
		    to 
		      Select to_char(Date_Field,"HH:MM A.M.")
		      into :block.time_field
		      from Table;

		  Set up a post-update trigger on the record to
	  Lock Record for Update
	  Update Record
	   set Date_Field = to_date (:block.Date_Field || :block.Time_Field, 
					   " Pick your best format ")
		where rowid = :rowid;

	   	 If your still stuck with SQL*Forms then
		  Preface the above trigger with

		  select :block.Date_Field || :block.Time_Field
		   into :hidden.Text80 from dual;
	

	(By the way this is not a problem that got solved in Version 3.0
	   or I havn't got it to work yet :-) )