Re: Problems with Date Field

Since you are using numeric fields for the entries, you have to convert the numbers to character with explicit format masks. Without a format mask, leading zeros are omitted. You need Ltrim to remove the leading space reserved for the sign. Also, don't EVER do a To_Date without using a format mask -- you're just asking for trouble.

    :REQUEST_LINEITEMS.EVENT_DATE := TO_DATE( LTRIM(TO_CHAR(V_DAY,'09'))

                                             ||
LTRIM(TO_CHAR(V_MONTHNUM,'09'))
                                             ||
LTRIM(TO_CHAR(V_YEAR,'0999')),
                                            'DDMMYYYY');

Steve Cosner

Scott Haley wrote:
>
> I'm working in Forms 5.0 and I'm having a problem with a data field.
> The way the system is set up the users enter the each part of the date
> (month, day, year) into separate textboxes. The text boxes have the
> autoskip property set to yes, this is the way the users wanted it. On
> the post text event of the year field I concatenate these together in
> the DD-MON-YYYY format and then try and stuff them into the event_date
> text box. But I keep getting an error (ORA-01830), I put my code below
> which constructs the correct date format from the three fields. Any
> help would be greatly appreciated.
>
> DECLARE
>
> V_MONTHNUM NUMBER(2);
> V_MONTHTXT CHAR(3);
> V_DAY NUMBER(2);
> V_YEAR NUMBER(4);
> V_DATE VARCHAR2(11);
>
> BEGIN
>
> V_MONTHNUM := :REQUEST_LINEITEMS.TXTMONTH;
> V_DAY := :REQUEST_LINEITEMS.TXTDAY;
> V_YEAR := :REQUEST_LINEITEMS.TXTYEAR;
>
> V_MONTHTXT := REMIT_PROC.MONTH_TEXT(V_MONTHNUM);
>
> V_DATE := V_DAY || '-' || V_MONTHTXT || '-' || V_YEAR;
>
> :REQUEST_LINEITEMS.EVENT_DATE := TO_DATE(RTRIM(LTRIM(V_DATE)));
> EXCEPTION
> WHEN OTHERS THEN
> MESSAGE (ERROR_TEXT);
>
> END;
>
> Thanks in advance,
> Scott
Received on Wed Nov 17 1999 - 19:57:56 CET