Hibernate version:3.1

I want to compare dates in HQL, my model uses java.util.Date to map a oracle DATE column. Basically I need:

-- Oracle SQL would be like this
select * from a where trunc(a.dateAndTime) between trunc(?) and trunc(?)

Hibernate does not allow me the use of "trunc()" because it maps the parameters as TIMESTAMP (when setting the preparedstatement parameter value) - and node that the database if of type DATE.

Please note that the "trunc()" function is needed because oracle's DATE field contains date and time, and that "trunc()" does not operate on TIMESTAMP's.

Thanks

Are you using query.setTimestamp() to set your params? If so, try using query.setDate() and then a query like:-

I am not, Hibernate is doing it in the prepared statement it creates.

My current HQL is this one:

select new com.bcsinfo.tsheet.model.TSCalendarValue(ts.Date,sum(ts.UT))
from TimeSheet as ts
where ts.Date between :dFrom and :dTo
group by ts.Date
order by ts.Date

This gives me a Calendar of the sums of ts.UT, and I have many other uses of date and between. This HQL should be (if you think of oracle and it's DATE type):

select new com.bcsinfo.tsheet.model.TSCalendarValue(ts.Date,sum(ts.UT))
from TimeSheet as ts
where trunc(ts.Date) between trunc(:dFrom) and trunc(:dTo)
group by ts.Date
order by ts.Date

But this does not work because of trunc does not operate on a timestamp in oracle. Ant it also seams wrong to me (not portable). I would like to do:

--WRONG
select new com.bcsinfo.tsheet.model.TSCalendarValue(ts.Date,sum(ts.UT))
from TimeSheet as ts
where date(ts.Date) between date(:dFrom) and date(:dTo)
group by ts.Date
order by ts.Date
-- End of WRONG

But "date()" does not exist, I could only find: day(), month(), year(). But since I am using parameters I am not even trying to use them.