Search between dates not working using criteria

MJ · **Joined:** Wed Dec 28, 2005 5:44 am **Posts:** 12

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Code:

List list = getSession().createCriteria(Department.class).
          add(Restrictions.between("deptDate",startDate,endDate);

Start Date - 11/21/2006
End Date - 11/21/2007

Returns records only till 11/20/2007 though there are records present on 11/21/2007.

Please advice where am i going wrong.

Thanks,
MJ

thatmikewilliams · **Posted:** Sun Nov 25, 2007 8:28 am

According to http://www.w3schools.com/sql/sql_between.asp the SQL operator "between" might exclude the test dates, depending on the db implementation.

The other thing to watch out for is dates that also contain time information. e.g. imagine the end date in the database represents the date "11/21/2007 06:55:20". Creating a date search field using "11/21/2007" will almost certainly set the time component to midnight, i.e. earlier than the date in the database, excluding that particular row.

MJ · **Joined:** Wed Dec 28, 2005 5:44 am **Posts:** 12

Thanks for the reply. So is there any other way to just check for the dates?

like we do in normal queries using where condition.

thatmikewilliams · **Posted:** Sun Nov 25, 2007 11:34 am

I'm assuming you want an inclusive search, i.e.
Start Date - 11/21/2006
End Date - 11/21/2007
returns rows where date is 11/21/2006 AND rows where date is 11/21/2007 (plus everything in between, obviously).

Code:

where deptDate >= start_date and deptDate < end_date

Note the different operators here: >= and <

To avoid the time issue, start_date should be midnight on the start day of the search, end_date should be midnight on the _day after_ the end date.

e.g. for the following
Start Date - 11/21/2006
End Date - 11/21/2007

Code:

where deptDate >= 11/21/2006 00:00:00 and deptDate < 11/22/2007 00:00:00

Note end date set to 11/*22*/2007

MJ · **Joined:** Wed Dec 28, 2005 5:44 am **Posts:** 12

Well,I know this works with normal HQL or SQL but i wanted to do it using criteria. Moreover i cannot give end date as 11/22/2007 as this search is done from the front end there is a condition where in the end date cannot be greater than today's date. So this wouldn't work.

I tried the following but not much success -

Code:

List list = getSession().createCriteria(Department.class).
              add(Restrictions.ge(deptDate,startDate)).
              add(Restrictions.le(deptDate,endDate)).
              list();

Thanks,
MJ

thatmikewilliams · **Posted:** Sun Nov 25, 2007 2:22 pm

Quote:

I tried the following but not much success -

What happened? Still didn't return the expected results? Are you setting the times to midnight in the date objects?

Perhaps it makes sense for the front end to prevent you entering dates in the future but that doesn't mean you can't mangle those dates at the point you do the search. Take the end-date entered by the user, add one day and set the time to midnight.

ramnath · **Posted:** Mon Nov 26, 2007 2:52 am

Hi,

The below between query worked for me with oracle backend...

criteria.add(Expression.between("dateField", model.getFromDate(), model..getToDate()));