Urgent ---- "not a valid month" Error

[fungling]

Dear all,

Following sql statement execute successfully in Oracle DBMS. However, "not a valid month" error occurs when I execute this statement in Java. What's wrong with this statement? Anything I should beware when using embedded sql in Java?

String test = "select pur_id, pur_date, confirmed,completed from purrec where pur_date between TO_DATE('12-12-02', 'DD-MON-YYYY') and TO_DATE('12-01-03', 'DD-MON-YYYY')";
ResultSet result=stmt.executeQuery(test);

Thanks for your help.

Joyce

 

[ringd]

Joyce,

Without looking at the API for this, is 'DD-MON-YYYY' a mask which is telling the call what format to expect the date? If so, the date you are providing (such as '12-01-03') is being parsed from left to right and the month field is the first one which does not match what the mask is saying (i.e. does 'MON' mean 'JAN', 'FEB' etc?). For your date format, should the mask be 'DD-MM-YY'?

Cheers,
Dave.
Mailto:dave.ring@barclays.co.uk

 

[pedrosolorzano]

Hi fungling:

Probably, the Connection class from the jdbc driver for oracle works with predefined variable values, for example the date format, it explains why you don't get the error directly on the db. Try to change the properties for the Connection, or better, use a PreparedStatement to set the Date field, so you can avoid these portability issues.

Please tell me if you need more information about the PreparedStatement class.

Hope it helps. Pedro Andrés Solorzano
Pontificia Universidad Javeriana
Bogotá, Colombia, SurAmérica.

 

[thekobbler]

Yes, TO_DATE('12-12-02', 'DD-MON-YYYY') should be..

TO_DATE('12-DEC-2002', 'DD-MON-YYYY')

took 30 seconds to find out on google... I wish people would attempt to solve their own problems.

 

[pedrosolorzano]

Hi all:

This is a forum, which is intended to solve problems. Pedro Andrés Solorzano
Pontificia Universidad Javeriana
Bogotá, Colombia, SurAmérica.

 

[thekobbler]

Yes, but developer should learn how to find solutions to their problems. I am not familiar with SQL statements, but it still only took me 30 seconds to find the answer. I believe it is very rare that a problem you may be having hasn't already been experienced, and is not therefore documented on the web... somewhere.

Forums, are a quick way to get an answer to a problem, as long as you learn from the answer. Getting someone else to do your homework, doesn't make you brighter!! As you search for the solution you read around the problem, and get a better understanding.

 

[pedrosolorzano]

Hi thekobbler:

I'm agree with you. But, think about it for one minute. What if somebody else have the same problem, and he types in google like you for a solution. Probably he or she will find the page that you are reading which you helped to build. So, this is a way to solve problems to many people, in just 30 seconds.

Dont you think? Pedro Andrés Solorzano
Pontificia Universidad Javeriana
Bogotá, Colombia, SurAmérica.

 

[fungling]

Hi thekobbler:

Sorry for my typing mistake in my message. Before getting help in this forum, I tried following statement too and searched the java api, javaworld.com and check the java reference book.

String test = "select pur_id, pur_date, confirmed, completed from purrec where pur_date between to_date('12-DEC-2002' ,'dd-mon-yyyy') and to_date('12-DEC-2003' ,'dd-mon-yyyy') " ;

The statement does work in oracle. However,'not a vaild month' error occurs when run in Java. So, It doesn't help to use to_date('12-DEC-2003', 'dd-mon-yyyy'). Besides, it is not caused by the date format between American English and Brithish English.

Secondly, I am feel lost becasue it is a pure sql statement. If it works in Oracle, it should work in java too.

I am solving this problem. If success, I will post it in the forum and wish others get help quickly.

Thanks in advance.

Joyce

 

[thekobbler]

Perhaps, you have to use the form TO_DATE(YYYY-MM-DD) in Oracle. See

http://philip.greenspun.com/sql/dates.html

 

[pedrosolorzano]

Hi fungling:

Did you tried the PreparedStatement? Pedro Andrés Solorzano
Pontificia Universidad Javeriana
Bogotá, Colombia, SurAmérica.

 

[flumpy]

the kobbler is correct. it isn't java thats the problem, its the setup of the location that the dbserver is set to. The sql engine is dealing with dates in the locale that the server is in, and wont understand '12-DEC-2003'.

2003-12-12 is international date format, and is understood all the time.