HQL Comparing Dates [duplicate]

Question

I have the following query:

    StringBuilder sb = new StringBuilder("select e from Event e");
    sb.append(" where e.user = :user and e.activated = true and e.startDate <= :date and uc.endDate >= :date");
    Query query = this.getEntityManager().createQuery(sb.toString());
    query.setParameter("user", user);
    query.setParameter("date", date);

Where date is a standard java date object and startDate, endDate are Postgresql Dates without times (eg. in the database they look like '2014-04-03')

But I am finding that when the supplied date parameter is the same day as startDate column then no matches are found. I thought that '=' in '<=' would deal with this?

I assume this is happening because one is a full timestamp and one is just a date? I have already tried suggestions from other similar questions including...

-Using SimpleDateFormat to convert the date to yyyy-MM-dd pattern then using DATE() to cast in the HQL

-Using Calendar to remove the time components before setting as a parameter

-Using day(e.startDate) <= day(date) AND month(e.startDate) = month(date) etc...

But none have worked. So my question is, why is this date comparison not working and/or how do I cast the parameter to a date correctly?

PostgreSQL 9.0 | JPA | Hibernate 3.6

Thanks!

Answer 1

Check out Query.setDate(), Query.setTimestamp() methods and use the correct one instead of Query.setParameter().

Answer 2

Something like below:-

Query q = em.createQuery("select o from LoadFileHistory o where o.finishDate > :today ");
q.setParameter("today",todaysDateObject,TemporalType.DATE);
q.getResultList();


select o from Operation o
where o.merchantId = :merchantId
and o.captureLimitDate < :maxDateTime
query.setParameter("maxDateTime", maxDateTime, TemporalType.TIMESTAMP);