Re: ORA-1866 The datetime class is invalid error...the saga continues!

From: Andy Kendall (ak)
Date: 03/01/04 

Date: Mon, 1 Mar 2004 16:27:50 -0000

Just a suggestion, but you could try using a different data provider. As I
understand it there are at least 4 options :
    Use the Microsoft ADO.NET Oracle Provider
    Use the Oracle ADO.NET provider
    Use the OLEDB or ODBC ADO.NET data provider
    Use a 3rd party Oracle ADO.NET provider

Theres a long history of problems with accessing Oracle data using Microsoft
Tools, so it may be worth trying a different provider.

"Malcolm Diaz" <mdiaz@planvista.com> wrote in message
news:FF58B0D3-2CDB-416C-BD5D-FFB7FE56DF00@microsoft.com...
> Hi all,
> I am still in need of help with this Oracle issue. Please read below
as I have copied my original request for help along with the 2 replies I
got. If any one can help me out at all I would be eternally grateful! I have
also included my response to both replies received in hopes that including
all this back ground info may be helpful to someone looking to help me out.
As always, thank you all for the help!
>
> Malcolm Diaz
>
>
> Original Post:
>
> Hello all,
> Thank you all in advance. I am completely at a loss here with this
error. I am developing a simple interface that will allow a user to
enter,execute and view results from a SQL statement to our oracle database.
I have found that I can not query more than a single day at a time when
using a date > 12/31/2002 as my where clause.
> For example;
>
> select * from tclaim where (process_date >= to_date('02/01/04',
'mm/dd/yy')) and (process_date < to_date('02/05/04','mm/dd/yy'))
>
> select * from tclaim where process_date >= '01-FEB-2004' and process_date
< '05-FEB-2004'
>
> Neither one of the above statements will execute unless I make the year
less than 2003 in my dates or only ask to return one day. I have poured over
several sites and articles without so much as a clue why this is happening.
Any help would be greatly appreciated as I am desparately trying to showcase
VB.NET to my employers and not being able to query data using a date field
in this calendar year is sure going to make this a hard sell!
>
> Thank you!!
>
> Reply 1:
>
> Hi Malcolm,
>
> You are talking about Oracle problem that has nothing to do with VB.NET.
> As a better approach you might use parametrised query.
> Something like:
> select * from tclaim where (process_date >= :fromDate) and (process_date <
> :toDate)
> cmd.Parameters.Add(":fromDate", OracleType.DateTime).Value = fromDate '
> insert your date here
> cmd.Parameters.Add(":toDate", OracleType.DateTime).Value = toDate ' insert
> your date here
>
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
>
>
> My Response to Miha Markic:
>
> Miha Markic,
> Actually, yes it's an error message returned by Oracle but both example
SQL statements I provided earlier will execute flawlessly in any enviornment
BUT my vb.net code. I've tried both statements in SQL*PLUS, SQL Work***,
and Tools For Oracle(aka Tora) with no problems at all. Only when using
VB.NET does it blow up on me. All this leads me to believe that it is VB.NET
that is experiencing a problem. The parametrised query is a good idea but
makes no difference....as long as I insist on asking for more than a single
days worth of data in 2003 or 2004 it will not execute. Can anyone at all
point me in a different direction?
> Again I thank you all in advance for what advice you may send me and to
those who have replied I am in your debt! Thank you!
>
>
> Reply 2:
>
> Hello,
> Thanks for your post. As I understand, the problem you are facing is that
a
> SQL statement does not work properly when using a date > 12/31/2002.
Please
> correct me if there is any misunderstanding.
>
> 1. Make sure that there are several rows with date > 12/31/2002 in your
> table.
>
> 2. To narrow down the problem, I suggest that you can execute the SQL
> statement in Oracle SQL Plus window and see if it works.
>
> 2. In addition, you can also try other date format in SQL statement. For
> example:
>
> select * from tclaim where process_date >= '2004-02-01' and process_date <
> '2005-02-01'
>
> I am standing by for your response.
>
> Regards,
>
> HuangTM
> Microsoft Online Partner Support
> MCSE/MCSD
>
> My Response to HuangTM:
>
> HuangTM,
> I've tried both statements in SQL*PLUS, SQL Work***, and Tools For
Oracle(aka Tora) with no problems at all. Only when using VB.NET does it
blow up on me. All this leads me to believe that it is VB.NET that is
experiencing a problem. I also played with using several different date
formats in my efforts but to no avail. Like before I can get the query to
execute perfectly anywhere but my vb.net enviornment. This is extremely
frustrating as this simple problem is causing me to lose my fight for VB.NET
in the work place. I can't justify recommending this product as long as a
simple SQL statment is going to cause so much problems. At this point I
would much rather have someone more versed in the language find a silly or
even an out right stupid mistake in my code as the problem than to have to
tell my employer that VB.net could handle a simple query. I refuse to
believe it can't be done.
>
> Again I thank you all for any help at all.
>
>