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.

score 44 · Accepted Answer · edited Sep 07 '15 at 08:34

44

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

edited Sep 07 '15 at 08:34

Sanghyun Lee

21,644
19
100
126

answered May 25 '11 at 10:21

JB Nizet

678,734
91
1,224
1,255

*Protip:* if you don't have to deal with the time component like the OP above, you could just use `le(..)` (instead of `lt(..)` + messy date arithmetic). – Priidu Neemre Apr 28 '20 at 13:08

score 2 · Answer 2 · answered Aug 26 '16 at 08:05

2

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

answered Aug 26 '16 at 08:05

boycod3

5,033
11
58
87

1

This gives error: "could not resolve property: DATE(auditDate)" – Mehmood Memon Mar 01 '20 at 06:37

score 1 · Answer 3 · edited Sep 16 '17 at 12:40

1

 criteria.add(Restrictions.ge("fromDate", DateUtil.persianToGregorian(currentDate)));
 criteria.add(Restrictions.le("toDate",  DateUtil.persianToGregorian(currentDate)));

 return criteria.list();

edited Sep 16 '17 at 12:40

Dario

6,152
9
39
50

answered Sep 16 '17 at 11:10

javacode1990

19
6

score 1 · Answer 4 · answered Aug 26 '19 at 06:16

add one more day to end date

 @Autowired
 private SessionFactory sessionFactory;

 String startDate = "2019-07-31 ";
 String endDate = "2019-08-24 ";

 Date fromDate = format.parse(fromDate);

 /* Add one more day to the end date*/

 Date to =format.parse(endDate);
 Calendar today = Calendar.getInstance();
 today.setTime(dateto);
 today.add(Calendar.DAY_OF_YEAR, 1);

 Date toDate= format.parse(format.format(today.getTime()));

 Criteria crit = sessionFactory.getCurrentSession().createCriteria(model.class);
 crit.add(Restrictions.between("dateFieldName", fromDate, toDate));
 List result = crit.list();

Restrictions Between for Date in Hibernate Criteria

4 Answers4

Linked