Re: Oracle Forms 4.5 and Date/Time fields

From: Steve Cosner <stevec_at_zimmer.CSUFresno.EDU>
Date: 1996/07/02
Message-ID: <4rc953$c1i_at_zimmer.CSUFresno.EDU>#1/1


In article <4rbidh$no8_at_news.ppg.com>, <Yourname_at_somwhere.COM> wrote:
>I have experience some problems with a time only field within Forms. I
>am using version 4.5.6.5.5 of Forms and 7.1 of the Oracle database.
>
>I have a date field in which I have masked to show on hour and minutes
>(HH24":"MI). When an update is made and saved to this field, the
>database is updated with the correct time, but the date is changed to the
>first day of the current month. Example 6/17/96 07:30 would become
>6/1/96 08:00 if the time was changed from 0730 to 0800.
>
>If the day and month a included within the mask or no mask is used, the
>problem goes away. Oracle support was of no help with this, they had
>never heard of this problem (BUG!).
>
>Has anyone heard of this and is there another work around other that
>showing the date and month.

I would expect Forms to do exactly what you are experiencing. If you don't specify the century on a form, you get the century of the client's date (using standard date format of DD-MON-YY). If you don't specify the time, you get 12:00:00. If you don't specify the month, you get the month of the client's date. Etc, etc... (I wonder who Oracle hires for their tech support?)

To remedy your situation, create a hidden datetime item in your block to store the full date and time. Be sure the format mask includes everything. Use the hidden item as your *real* base table item to select into and update from. (In the example below, I named it :Blk.Hidden_Time)

Create a non-base-table item on the form for the user to see and modify (called :Blk.Visual_Time). In a post-query trigger, display the time in your item:

     :Blk.Visual_Time := :Blk.Hidden_Time.

In a When-Validate-Item trigger on Blk.Visual_Time, do this:

  :Blk.Hidden_Time := To_Date(To_Char(:Blk.Hidden_Time,'MMDDYYYY')

                           || To_Char(:Blk.Visual_Time,'HH24MI')
                                ,'MMDDYYYYHH24MI');

This ought to work.
Regards,
Steve Cosner Received on Tue Jul 02 1996 - 00:00:00 CEST

Original text of this message