Re: Oracle and PeopleSoft date format problem

[Aya Fall <afall@NS.SYMPATICO.CA>]

Jennifer LEE wrote:
>
> Hello!
> I am new to writing SQRs.
> The problem I have is with a date field to be loaded
> from a flat file into a PeopleSoft table.
>
> The environment that I am working in has the following elements:
> Window 95, Novell NT, SQRW V3.0.18.1.1, PeopleSoft Tools 7, and Oracle 7 DBMS.
> In PeopleSoft, a date field is defined as 10 characters long whereas
> the default date format in Oracle is DD-MMM-YYYY.
> I have tried different ways to format the date field value in my SQR.
> Each time I got a different error message.
> Has any one of you experienced a similar problem?  How was your problem 
>solved?
>
> SQL Status = -1400, SQL Error  = ORA-01400: mandatory (NOT NULL) column is 
>missing or NULL during insert
> Case 1:
> begin-sql on-error=sql-error
> INSERT INTO PS_TBL (Date_Field) Values(to_date('10101900','mmddyyyy'));
> end-sql
> Case 2:
> begin-sql on-error=sql-error
> INSERT INTO PS_TBL (Date_Field) Values('10_Oct_00');
> end-sql
>
> SQL Status = -1858, SQL Error  = ORA-01858: a non-numeric character was found 
>where a numeric was expected
> Case 3:
> begin-sql on-error=sql-error
> INSERT INTO PS_TBL
> (Date_field)
> Values(to_date('1900-10-10','yyyy-mm-dd'));
> end-sql
> end-procedure
>
> SQL Status = -1830, SQL Error  = ORA-01830: date format picture ends before 
>converting entire input string
> Case 4:
Hi Jennifer:

Use the following Oracle date format in your SQR. Example:
    14-APR-98 (not 14-APR-1998)

Hope this helps.

Aya.

> INSERT INTO PS_TBL
> (Date_field)
> Values('10_Oct_1900');
> end-sql
> end-procedure
>
> Thanks in advance!
>
> Jennifer (Jen) Lee
> 626-1679