Hello,

I am writing a native query which has some comparisions with dates, and it does not work through hibernate but works on the PL/SQL developer.

My code is as follows :
SQLQuery q = session.createQuery(" select {x.*}, {y.*}, {z.*} from X x, Y y, Z z
where to_char({x}.date, 'mm/dd/yyyy hh24mi') >= to_char(:date, 'mm/dd/yyyy hh24mi') ");

q.addEntity("x", X.class);
q.addEntity("y", Y.class);
q.addEntity("z", Z.class);

if (date != null)
q.setDate("date", date);

List l = q.list();
return l;


The above query does not return the correct resultset because of the way hibernate translates :
to_char({x}.date, 'mm/dd/yyyy hh24mi') >= to_char(:date, 'mm/dd/yyyy hh24mi')

When I tried to to replace the above query with this
select to_char(:date, 'mm/dd/yyyy hh24mi') from dual;
It returns me 05/20/2010 0000
instead of 05/20/2010 1025

Not sure if this is a bug in Hibernate.
The only work around I could think was to format the date in java and pass it to the query.
Has anyone faced this problem before ?

Thanks,
Gayatri

I am not sure this will help... but I think the setDate() is for the date-only part, setTime() for the time-only part and setTimestamp() for complete date + time.

That worked. Thank you.

q.setTimestamp("date", date);

Thanks,
Gayatri