CODEWITHSUNDEEP
javahibernatejpa
user1727939
user1727939

Reputation:

JPA : How to compare two dates

I need to fetch data between two dates in a JPQL query but it doesn't work. The dates are in integer but i want to compare with Dates. Here is partial query,

Thanks! 

     Query query = em.createQuery
    ("select p1 from Profile p1, Preference p2 where 
(p1.birthdate >= p2.agefrom and p1.birthdate <= p2.ageto and p2.preferenceid=:a)")
                                        .setParameter("a", profileid);


                TABLE Profile {
                profileid integer,
                birthdate date
                }

                TABLE Preference {
                preferenceid integer,
                agefrom integer,
                ageto integer
                }

Upvotes: 0

Views: 947

Answers (2)

jonaps
jonaps

Reputation: 1

I don't think you can compare Date and Integer. You have to either :

  • convert the birthdate into integer using datediff(year, birthdate, getDate())
  • or convert the agefrom into Date using dateadd(year, -agefrom, getDate()),
  • or you can simplify it using datediff(year, birthdate, getDate()) between agefrom and ageto.

Upvotes: 0

Masudul
Masudul

Reputation: 21981

Use BETWEEN

Query query = em.createQuery("select p1 from Profile p1, Preference p2 where 
   p1.birthdate BETWEEN p2.agefrom AND p2.ageto and p2.preferenceid=:a)")

Upvotes: 2

Related Questions