kabugima2 kabugima2 - 1 year ago 103
Java Question

Using Like Expression for Date Field using JPA 2.1

Am trying to perform a query with LIKE expression on a date field using a named query as shown below ;

public Users getResultsByDate(Date regDate){
List<Users> dbUsers = new ArrayList<Users>();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Query searchQuery = getEntityManager().createNamedQuery("getUserByRegDate");
searchQuery.setParameter("regDate", "%"+dateFormat.format(regDate)+"%");
dbUsers = searchQuery.getResultList();
catch(Exception ex){
dbUsers = new ArrayList<Users>();
return dbUsers;


The name query in corresponding class is;

@NamedQuery(name = "getUserByRegDate", query = "SELECT user FROM Users user WHERE user.regDate LIKE :regDate"),

The query was working using previous JPA 2.0 version. I get the following error when i execute the same when running on JPA 2.1;

You have attempted to set a value of type class java.lang.String for
parameter :regDate with expected type of class java.util.Date ...

How can i resolve this when running on JPA 2.1 ? It's Working OK in JPA 2.0.

Answer Source

Try with a native named query, transforming user.reg_date to string and then comparing it with the operator like to the parameter. In case of an Oracle DB it would be for example:

@NamedNativeQuery(name = "getUserByRegDate", query = 
  "SELECT user FROM Users user WHERE TO_CHAR(user.reg_date, 'DD-MON-YYYY') LIKE :regDate"),
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download