Re: Problems with Date Field

Scott,
  Try this. Everything in the TO_DATE() must be in character. Adding 100 to the day and SUBSTR()ing the last 2 characters allows for the possibility of a user entering a single day number.

DECLARE

  V_MONTHNUM NUMBER(2);
  V_MONTHTXT CHAR(3);
  V_DAY CHAR(2);
  V_YEAR CHAR(4);
  V_DATE VARCHAR2(11);

  V_MONTHNUM := :REQUEST_LINEITEMS.TXTMONTH;
  V_DAY      := SUBSTR(TO_CHAR(:REQUEST_LINEITEMS.TXTDAY + 100),2,2);
  V_YEAR     := TO_CHAR(:REQUEST_LINEITEMS.TXTYEAR);
  V_MONTHTXT := REMIT_PROC.MONTH_TEXT(V_MONTHNUM);
  V_DATE     := V_DAY || '-' || V_MONTHTXT || '-' || V_YEAR;

In article <3832CD64.CA5E7089_at_pilot.msu.edu>,   haleysco_at_pilot.msu.edu 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 18 1999 - 01:56:32 CET