Re: Oracle Dates through Data Provider - ORA-01858

paul_a_long_at_hotmail.com (Paul Long) wrote in message news:<4c46b5c8.0406291246.6309341a_at_posting.google.com>...
> Hi,
>
> I'm having a nightmare when trying to insert records into an Oracle
> database that contain dates. I've believe I should be using the
> to_date() function but I continually get the error:
>
> "ORA-01858: a non-numeric character was found where a numeric was
> expected"
>
> I'm reading from a text file and the datetime values are as follows:
>
> 12-DEC-2003 12:01:22
> 12-DEC-2003 12:01:23
> 12-DEC-2003 12:01:24
> 12-DEC-2003 12:01:25
>
> If I carry out straight forward SQL upon the database I can insert
> records without any problems. E.g.:
>
> INSERT INTO MyTable (Name, OccurrenceTime)
> VALUES ('Test', to_date('31-Dec-2004 10:47:50', 'DD-MON-YYYY
> HH:MI:SS') );
>
> However, my problem is achieving the same results from my C#
> application that uses the DataTable structure and OracleDataAdapter
> object.
>
> The DataTable's Rows.List[0] contains:
>
> ItemArray[0] = "Test"
> ItemArray[1] = "to_date('31-DEC-2004 10:47:50', 'DD-MON-YYYY
> HH:MI:SS')"
>
> However, when I call the Update() method of OracleDataAdapter and pass
> the DataTable as the parameter I get the ORA-01858 error.
>
> Can I pass: 'to_date(....)' via the DataTable? It appears from what
> I've found on the newsgroups that you cannot pass a literal date
> straight into Oracle.
>
> Please, if anyone can help, I would very much appreciate it.
>
> Regards,
>
>
>
> Paul.

Paul, I am not familiar with the tool you are using but does your program error immediately or is it processing for a little while before it dies? If it is processing then you might just have some invalid data in some of your input.

Second check the date data to see if 24 hour clock is in use or if you need to account for an AM/PM indicator. For the 24 hour clock you would need HH24 in your format mask.

The idea of what might be messing you up is to check to see if the low digit days are entered as '01' or just '1'. If may be that the tool has a problem related to number of digits for the first through ninth.

Good Luck,
-- Mark D Powell -- Received on Thu Jul 01 2004 - 15:55:00 CEST