ORA-1866 The datetime class is invalid error

[Malcolm Diaz]

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!!

[Miha Markic [MVP C#]]

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

"Malcolm Diaz" <md***@planvista.com> wrote in message
news:AA**********************************@microsof t.com...

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!!

[Tian Min Huang]

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

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

[Malcolm Diaz]

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 Worksheet, 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

----- Miha Markic [MVP C#] wrote: ----

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 her
cmd.Parameters.Add(":toDate", OracleType.DateTime).Value = toDate ' inser
your date her

--
Miha Markic [MVP C#] - RightHand .NET consulting & software developmen
miha at rthand co
www.rthand.co

"Malcolm Diaz" <md***@planvista.com> wrote in messag
news:AA**********************************@microsof t.com..

Hello all
Thank you all in advance. I am completely at a loss here with thi error. I am developing a simple interface that will allow a user t
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 whe
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_dat < '05-FEB-2004 Neither one of the above statements will execute unless I make the yea less than 2003 in my dates or only ask to return one day. I have poured ove
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 showcas
VB.NET to my employers and not being able to query data using a date fiel
in this calendar year is sure going to make this a hard sell Thank you!

[Malcolm Diaz]

I've tried both statements in SQL*PLUS, SQL Worksheet, 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 reccommending 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.