larrys@crg5.UUCP (Larry Scheurich) (06/29/90)
> From sequent!tektronix!zephyr.ens.tek.com!uw-beaver!cornell!batcomputer!rpi!zaphod.mps.ohio-state.edu!usc!cs.utexas.edu!uunet!ncrlnk!ncrwic!sfc!chas Thu Jun 28 21:28:43 PDT 1990 > Article 6278 of comp.databases: > Path: crg5!sequent!tektronix!zephyr.ens.tek.com!uw-beaver!cornell!batcomputer!rpi!zaphod.mps.ohio-state.edu!usc!cs.utexas.edu!uunet!ncrlnk!ncrwic!sfc!chas > >From: chas@sfc.Wichita.NCR.COM (Charles Binford) > Newsgroups: comp.databases > Subject: Date/Time in Oracle SQL-Forms, how to do it? > Keywords: Oracle SQL-Forms > Message-ID: <690@sfc.Wichita.NCR.COM> > Date: 28 Jun 90 14:19:18 GMT > Distribution: na > Organization: NCR Corporation, Wichita, Kansas > Lines: 17 > > 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> > This is a battle that I've recently encountered with SQL*Forms. I did a lot of checking, and all responses indicated that there is no good way to process dates in Oracle's SQL*Forms. The following code is from a .inp file of one of my forms. In order to validate the date/time, you need two character fields (one for date and the other for time). Then, you need to set up a non-display date field (mine is TMP_START_DATE) that you can convert the two characters fields into using the to_date function. The only problem with this is that you can't avoid the ugly SQL*Forms error when a user enters a bad date. Anyway you look at it, it's not an easy thing to do. I've tried lots of other workarounds, but nothing I could find works. ;Field name : START_DATE ;Type of field : CHAR ;Length of field / Display length / Query length : 9 / 9 / 9 ;Is this field in the base table Y/N : Y ;Is this field part of the primary key Y/N : N ;Default value : ;Page : 1 ;Line : 3 ;Column : 16 ;Prompt : ;Allow field to be entered Y/N : Y ;Allow field to be updated Y/N : Y ;SQL> **POST-CHANGE / ;SQL> select to_date(:FND_CONCURRENT_BATCH.START_DATE,'DD-MON-YY') into :FND_CONCURRENT_BATCH.TMP_START_DATE from dual ;Message if value not found : Invalid date format. Please use DD-MON-YY ;Must value exist Y/N : Y ;Is field mandatory Y/N : Y ;Is field fixed length Y/N : N ;Auto jump to next field Y/N : N ;Convert field to upper case Y/N : Y ;Help message : ;Lowest value : ;Highest value : ;Field name : START_TIME ;Type of field : CHAR ;Length of field / Display length / Query length : 8 / 8 / 8 ;Is this field in the base table Y/N : Y ;Is this field part of the primary key Y/N : N ;Default value : ;Page : 1 ;Line : 3 ;Column : 26 ;Prompt : ;Allow field to be entered Y/N : Y ;Allow field to be updated Y/N : Y ;SQL> **POST-CHANGE / ;SQL> select to_date(:FND_CONCURRENT_BATCH.START_TIME,'HH24:MI:SS') into :FND_CONCURRENT_BATCH.TMP_START_TIME from dual ;Message if value not found : Invalid time format. Please user HH:MI:SS where HH is 0-23 ;Must value exist Y/N : Y ;Is field mandatory Y/N : Y ;Is field fixed length Y/N : N ;Auto jump to next field Y/N : N ;Convert field to upper case Y/N : Y ;Help message : ;Lowest value : ;Highest value : -- Larry Scheurich uunet!sequent!larrys Sequent Computer Systems (503)-526-4240 Beaverton, OR "Smile, it makes people wonder what you're doing"