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 :-) )