HQL Comparing Dates

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!

Sorry guys, the issue was actually a different one....I have posted a new question here http://stackoverflow.com/questions/22868648/postgresql-date-type-and-java-simpledateformat — DaveB, Apr 04 '14 at 16:55

score 7 · Answer 1 · answered Apr 03 '14 at 19:15

7

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

answered Apr 03 '14 at 19:15

Zsolt Süli

216
2
7

2

Sorry, I should have mentioned, I am using JPA so its javax.persistence.Query not org.hibernate.Query so I cant use those methods! – DaveB Apr 04 '14 at 08:18

score 4 · Answer 2 · answered Apr 04 '14 at 08:34

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);

HQL Comparing Dates

2 Answers2