Re: SQL*Forms and the 21st Century

In article <27_at_pskl.win-uk.net>, pete_at_pskl.win-uk.net says...
>
>A good way I have seen to handle dates is to keep the date in a
>hidden item and have a non-table item for the data which is of type
>char, not date.
>
>You then have a when-validate-item trigger on this item which
>sorts everything out. This is a long winded way as you have to
>code all the triggers for new values, values returned from queries
>and values entered in enter query mode.
>
>However this is worth doing if you do what we did.
>
>All the users seemed to have different preferences as to how they
>entered the date, eg
>'7/10/95','07/10/95','7.10.95','7/JUL/95','7-JUL-95','7-JUL-1995'
>etc.
>
>We wrote a library procedure that checked what format had been
>entered and re-formatted it to '07-JUL-1995'.
>Depending on the application it may also be possible to guess
>which century they wanted, eg if 95 was entered it is 1995, if 10
>was entered it is 2010.
>
>Pete Kolton

excellent suggestion Pete. It solves the problem of allowing dates to be entered in any format.

There were serveral other good responses to my original post, but I'm surprised that most missed one of the real problems. In Forms 3 all fields, including dates, are in character representation until post time when they are converted to the correct format. My on validate trigger, which effectively added 100 years to certain dates, had no effect because the default format was only 2 digits for the year. No mater how many years were added to the date, it would always be interpreted as occuring in the 20th century when it was posted!

Some people mentioned the RR format, which is new to Oracle 7 and is not supported by Forms 3. My reading of the Oracle 7 pocket SQL guide was that this is always interpreted as occuring in the 21st Century. This would seem to create a whole new set of problems.

It seems that there are only two solutions to the century problem. One is to force users to enter all four year digits. The other is to program an on insert trigger to handle the century logic and do the posting.

Many thanks to all who responded.

Jack Received on Fri Jul 14 1995 - 00:00:00 CEST