Re: Insert Error- Date Problem

[Denise White <dewhite@VICR.COM>]

Hi Jason,

I really don't think that the problem is with the null dates, but how you are
handling the date that you mention in your original message.  To insert a null
date, I always have used a value of '' (two single quotes with nothing between
them) with no problem.

As someone else mentioned, you are taking a string value from your flat file,
and then trying to convert this from a 'date' into a string, when it is already
a string to begin with.  I think that your error message has to do with the fact
that you are treating it as a date, and it is not a valid native format date.

I believe that if you declare a variable as a date variable, and then use a
strtodate() function on the string from your flat file, you should then be able
to insert the resulting date variable.  For example, $datevar =
strtodate($inputdate,'yyyymmdd'), and then insert $datevar.

Another way is to use the following in your insert statement, without using any
intermediate variable:
insert into mytable ...... to_date($inputdate,'yyyymmdd') ....
This is actually the way I would prefer to do it.

HTH,
Denise White
Sr. Programmer/Analyst
Vicor
------------------------------

Date:    Thu, 28 Feb 2002 12:12:39 -0500
From:    Jason Rupp <jrupp@TECHNEGLAS.COM>
Subject: Re: Insert Error- Date Problem

Arun,

Thanks for the response.  I think the problem that I am having is the
default date values.  I am not sure how to assign a NULL value.  Do you
have any suggestions of what I can do to make the default date values
NULL?

Thanks!

>>> arunkumar13@YAHOO.COM 02/28/02 11:41AM >>>
use on-error option on the begin-sql and display or
show all the date variable to check whether all your
date variable are in correct format for Oracle.
For default date values use only null.

HTH
Arun
--- Jason Rupp <jrupp@TECHNEGLAS.COM> wrote:
> Hello All!
>
> DATABASE = Oracle 8
>
> I wrote a simple sqr that reads a flat file and
> inserts into a
> PeopleSoft table (PERSONAL_DATA).  The flat file
> only has about 12
> fields so I am inserting default values or spaces
> for the other 100+
> fields in the table.  The program is reading the
> file perfectly but when
> it goes to insert, I get an error:
>
> (SQR 5528) ORACLE OCIStmtExecute error 1847 in
> cursor 5:
>    ORA-01847: day of month must be between 1 and
> last day of month
>
> Error on line 203:
>    (SQR 3735) Could not execute SQL.
>
> There are about 10 date fields on this record but I
> am only inserting
> values for 2 of them.  For the other 8 date fields,
> I tried inserting
> NULL, '', and ' ' and had no luck.
>
> When I read the date off the flat file, the string
> looks like this:
> 19700101
>
> I then do a datetostr using the mask of 'DD-MON-YYY'
> so the value being
> inserted is '01-JAN-1970'.  I don't think this is
> the problem though.
>
> Does anyone have any ideas?  I appreciate your time.
>
> Thanks,
> Jason