Date problem in where clause

[Steve Renard]

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]

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]

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]

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]

Thanks Jan,
I used tochar function to make the comparison..
It worked great..

[Steve Renard]

Thanks Jan,
I used tochar function to make the comparison..
It worked great..