wood burning stoves 2.0*
The moose likes JDBC and the fly likes Date problem in where clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Date problem in where clause" Watch "Date problem in where clause" New topic
Author

Date problem in where clause

Steve Renard
Ranch Hand

Joined: Apr 27, 2007
Posts: 66
I'm trying to run a select query from a java class.
The query is built as a simple string based on user's input.
However, I get ORA-01843: not a valid month error when I have date values inside where clause..
When I copy the same query and execute in Oracle DB viewer it works perfectly..
What could be the problem?
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Steve,

the problem is that you did not post any code (yet).

Also: do your java class and the Oracle DB viewer use the same driver?

Herman
Steve Renard
Ranch Hand

Joined: Apr 27, 2007
Posts: 66
This is the query:
String query="select PRCS_DT from PURCH_TRANS where PRCS_DT='4/5/2007'" ;
rs = stmt.executeQuery(query);
It works fine when copied and executed in DB viewer..
I'm also sure that both java and Oracle use the same driver..
Please help..
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Check Oracle's data format.

The standard format is: DD-MON-YYYY.
Where
DD = two digit day number
MON = three character month code (localized in your Oracle NLS language)
YYYY = four digit year.

In your example: '04-MAY-2007'
If you want to use another date format, refer to Oracle's to_date function.
Your coud use something like ... to_date('04/05/2007', 'DD/MM/YYYY')...

Regards, Jan
[ June 23, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
youtube channel
Steve Renard
Ranch Hand

Joined: Apr 27, 2007
Posts: 66
Thanks Jan,
I used tochar function to make the comparison..
It worked great..
Steve Renard
Ranch Hand

Joined: Apr 27, 2007
Posts: 66
Thanks Jan,
I used tochar function to make the comparison..
It worked great..
 
wood burning stoves
 
subject: Date problem in where clause
 
Similar Threads
select distinct
Problems in accessing the Date Field
Delayed result with 'not like' clause in Where clause
Having trouble using Dates in SQL
Problems in accessing the Date Field