Dear All,

Can anyone share his/her experience of how to compare date of Oracle
DB thru Hibernate query statements? I always have the sql exceptiom
ORA-01861, literal does not match format string.

My query string just likes "select ... from ... where ... between '2004-01-01'
and '2004-04-01'.... ".

I use Hibernate 2.1.2 and Oracle 9i.

any suggestions?

Jun-Liang

You have to use the Oracle todate() function in the where clause of HQL if you work with strings. Why not have a java.util.Date object and bind it as a parameter?

Thanks for your help!

But could you elaborate the sentence "Why not have a java.util.Date object and bind it as a parameter?" My real situation is I need to query
from several tables and one of where clause including statements about
comparing date in Oracle DB and considering future of portability of code
we think it is better stick to Hibernate and not using any particular native db
functions. Any comments? or simple example?


Jun-Liang

Read the documentation about parameter binding. You can even read any JDBC documentation, its the same.

I got it! Thanks!

Jun-Liang

Would it be acceptable to manually set the NLS_DATE_FORMAT environment constant with a jdbc statement like this one in your case:

Alter Session set NLS_DATE_FORMAT = 'YYYY-DD-MM'

prior to any query (needed only once) ?

Or would it be more advisable to set it directly in the database server itself ?

We set our NLS_DATE_FORMAT on the server. It's much less of a hassle, in case you forget to code, etc. And it gives you one central location to change it.