HQL to query records between two dates

Question

I am trying to query all customers records using HQL in my Spring/ Hibernate app that have DateAdded between Date1 and Date2 OR LastSeen between Date1 and Date2, so I've build this HQL query in the Repository/ DAO class:

sessionfactory.getCurrentSession().createQuery("from Customer c where c.dateAdded BETWEEN '"+startDate+"' AND '"+endDate+"' OR c.lastSeenDate BETWEEN  '"+startDate+"' AND '"+endDate+"'").list();

I've debugged the app to check the startDate and endDate and found that they are sent as:

startDate: Wed Jan 22 01:16:57 HKT 2014

endDate: Wed Jan 29 01:16:57 HKT 2014

In DB, I am 100% sure there is one record at least meeting this query, as this record DateAdded and LastSeen are as follows:

2014-01-23 15:33:38

2014-01-25 15:33:38

So can someone please tell me what I am doing wrong / missing here?

Answer 1

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String frmDate = format.parse(startDate);
String enDate = format.parse(endDate);
sessionfactory.getCurrentSession()
.createQuery("FROM Customer AS c WHERE c.dateAdded BETWEEN :stDate AND :edDate ")
.setParameter("stDate", frmDate)
.setParameter("edDate", enDate)
.list();

hope this will help!

Answer 2

It's important to know that when using BETWEEN “setDate” will truncate the HQL date value passed as parameter and ignore the hours, minutes, seconds. This is very important to note especially if you have an HQL query checking between dates from different days because using “setDate” will interpret the date interval as between midnight of the specified dates.

The solution is to use “setParameter” instead of “setDate” which causes the HQL date values to be interpreted as dates and times.

here's a few examples in my site for those interested http://www.coding-dude.com/wp/java/hql-date-datetime-quick-tip/

Answer 3

I had similar issue. When we use end date (Wed Jan 29), the system looks for Wed Jan 29 00:00:00, which is just like choosing Jan 28. In order to avoid such case, we can add one day to the end date. This also explains why we do not have issue with the start date.

Surprisingly, this issue does not exist when we use hibernate criteria.

Answer 4

SimpleDateFormat formatter = new SimpleDateFormat("MM-dd-yyyy");
    Calendar c = Calendar.getInstance();
    c.setTime(new Date()); // Now use today date.
    c.add(Calendar.DATE, 90);
    Date fromDate = null, toDate = null;
    String fromDateStr = formatter.format(new Date());
    String toDateStr = formatter.format(c.getTime());
    try {
        fromDate = formatter.parse(fromDateStr);
        toDate = formatter.parse(toDateStr);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    query.setParameter("stDate", fromDate);
    query.setParameter("edDate", toDate);

Answer 5

Try something like this:

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date fromDate = df.parse(startDate);
Date toDate = df.parse(endDate);
Criteria criteria = sessionfactory.getCurrentSession().createCriteria(Customer.class)
   .add(Restrictions.between("dateAdded", fromDate , toDate ));
List<Customer> listCustomer = criteria.list();

Hope this helps..!!

Answer 6

SimpleDateFormat sf=new SimpleDateFormat("dd-MM-YYYY");
String fromDate=null;
String toDate=null;
fromDate=sf.format(startDate);
toDate=sf.format(endDate);
sessionfactory.getCurrentSession().createQuery("from Customer where dateAdded BETWEEN '"+startDate+"' AND '"+endDate+"'");