Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Date problem in where clause

 
Steve Renard
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 387
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Steve Renard
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jan,
I used tochar function to make the comparison..
It worked great..
 
Steve Renard
Ranch Hand
Posts: 66
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jan,
I used tochar function to make the comparison..
It worked great..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic