.Net Data Provider error help

[Ted]

I've used the Microsoft .Net Data Provider for Oracle and also the Oracle
..Net Data Provider . I'm issuing a simple select statement which would
return a list of dates formatted as such 'MM/DD/YYYY'. The table is storing
the dates in a field of date data type in the format of M/D/YYYY. There is
no time portion on any of the dates, the data was imported from SQL Server.
Here is the section of my statement which formats the date '
TO_CHAR(tblarInvoiceHeader.ARInvoiceDate,'MM/DD/YYYY')', I get the following
error:

ORA-01834: not a valid month

I can issue the statement via SQL + and TOAD, and not generate the error, it
has to be the data provider. Does anyone have a clue as to what's going on
and the panacea to this issue?

Best Regards,

Ted.

 

[Guest]

Hi Ted,

I have no problems with the TO_CHAR while using the MS Oracle Provider.
Have you tried to create a small sample app that will duplicate this
problem? If you can can you post the complete sample with the complete SQL
Statement so the group may help you diagnose the problem better. I have
pasted a sample below to show you what I mean.

BTW: I am using version 1.0.5 of the OracleClient dll and Oracle 9.2.0.5.0

I hope this helps.
----------------------
string sql = @"select TO_CHAR(b.TEST_DATE,'MM/DD/YYYY') from TEST b";
OracleConnection conn = new OracleConnection(strConn);
OracleCommand cmd = new OracleCommand(sql,conn);
conn.Open();
Object o = cmd.ExecuteOracleScalar();
if(o!=null)
Console.WriteLine(o.ToString());
conn.Close();

 

[Ted]

Brian, I agree with your solution and it should work fine, but I can't for
the life of me figure this out...here is my statement:

select distinct TO_CHAR(h.ARInvoiceDate,'MM/DD/YYYY') as ID, null as DT
from tblarInvoiceHeader h inner join trefgenBatchControl b on h.fkBatchID
= b.pkBatchID Where h.fkARTransTypeID in ('IN','MI','NS') and
h.PrintInvoiceYN = -1 order by TO_CHAR(h.ARInvoiceDate,'MM/DD/YYYY') desc

This statement simply populates a data gird.

If I remove the format 'MM/DD/YYYY', my grid is fills with the date like:
30-OCT-02. I can not get it to format correctly. I also have several other
statements written the same way with no problems I get the data as expected.
The only difference with this statement is that the field ARInvoiceDate, has
only the date like: M/D/YYYY in it and the others have the date and time.
Could this be the issue? I've exaughested many resources to no avail. Hope
you or someone can shed some light on this issue.


Ted

 

[Guest]

Hi Ted,
I don’t think that is your issue but I could be mistaken. I cannot
reproduce the results you are seeing. Here is what I did. I created a test
table with only a date field. I then populated the date field with 10 values
with the format just as you specified (e.g. 8/1/1999, 9/2/1999, etc…). I
took the query that you posted and pasted in my field names keeping
everything intact the way it was posted (except I deleted out the join to
narrow down further). I then created a console application with the code
below (basically the same as before except with a reader this time and your
modified query). It returned and formatted the data for me perfectly. Can
you try this same thing with a test table and then with the "suspect" table
(with the modified query) then post your results? Maybe that will help us
narrow the problem down.

Good Luck
-----------------
string sql = @"select distinct TO_CHAR(b.TEST_DATE,'MM/DD/YYYY') as ID,
null as DT from TEST b order by TO_CHAR(b. TEST _DATE,'MM/DD/YYYY')
desc";
OracleConnection conn = new OracleConnection(strConn);
OracleCommand cmd = new OracleCommand(sql,conn);
conn.Open();
OracleDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
Console.WriteLine(rdr.GetValue(0));
conn.Close();