Restrictions Between for Date in Hibernate Criteria

Question

Hello I am using hibernate in my example.For bean Table Audit Trial I want to fetch audit trial between a date range with inclusion of upper & lower limits. My code is like below

Criteria criteria = session.createCriteria(AuditTrail.class);  criteria.add(Restrictions.between("auditDate", sDate, eDate)); 

My starting date is 25/11/2010. and end date is 25/05/2011.But it is only giving the result up to 24/05/2011.It is not performing inclusive search.Any other way to do this. I am using SQL server.

Answer 1

I assume your auditDate is in fact a timestamp. If it's the case, then this is normal, because 25/05/2011 means 25/05/2011 at 0 o'clock (in the morning). So, of course, every row having an audit timestamp in the date 25/05/2011 is after 0 o'clock in the morning.

I would add 1 day to your end date, and use auditDate >= sDate and auditDate < eDate.

criteria.add(Restrictions.ge("auditDate", sDate));  criteria.add(Restrictions.lt("auditDate", eDate)); 

Answer 2

criteria.add(Restrictions.between("DATE(auditDate)", sDate, eDate)); use this for ignoring time from date.