aspose file tools*
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
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: 2510
    
  10

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..
 
 
subject: Date problem in where clause