EJB Query Language where condtion based on date
Hi,
I am using EJB3 query language. In the table I have a column called requestdate which is in date time format. I have created an entity class.
I need to select few records using query language(named queries) based on this requestdate matching to the current date. I want to compare only based on Date value (truncating the time). Something like this
select max(o.req_id) from requests o where o.requestdate = :currentdate
currentdate is java.sql.Date value formatted to yyyymmdd only.
How can I do this using query language?
Please help to me get this done.
thanka
Anuradha
# 1
Hi Anuradha ,
I'm afraid the query language does not support retrieving the date portion (i.e. truncating the time) from a date field.
I propose you turn you named into a BETWEEN query taking two parameters one for the beginning of the day and another for the end of the day. Then your named query could look like:
SELECT max(o.req_id) FROM requests o WHERE o.requestdate BETWEEN :start AND :end
The following code creates two calendar instances for the current day, one for 00:00:00 and another for 23:59:59:
Calendar start = Calendar.getInstance();
start.set(Calendar.HOUR_OF_DAY, 0);
start.set(Calendar.MINUTE, 0);
start.set(Calendar.SECOND, 0);
start.set(Calendar.MILLISECOND, 0);
Calendar end = Calendar.getInstance();
end.set(Calendar.HOUR_OF_DAY, 23);
end.set(Calendar.MINUTE, 59);
end.set(Calendar.SECOND, 59);
end.set(Calendar.MILLISECOND, 999);
Here is some sample code that creates a query instance for a named query called findMaxRequestPerDay and passes the Date of the above Calendar instance as actual parameter values. It assumes the named query is called findMaxRequestPerDay and the req_id field is a long:
Query query = em.createNamedQuery("findMaxRequestPerDay");
query.setParameter("start", start.getTime());
query.setParameter("end", end.getTime());
Long max = (Long)query.getSingleResult();
I hope this helps.
Regards Michael
