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

You need to annotate with @JsonFormat the same way the date filters and date field as follows:

Class Filter {
...
@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="dd/MM/yyyy HH:mm", timezone="America/Sao_Paulo")
private Date startDate;
}

Class YourObject {
...

@JsonFormat(shape=JsonFormat.Shape.STRING, pattern="dd/MM/yyyy HH:mm", timezone="America/Sao_Paulo")
private Date DateAdded;
}

The pattern and timezone should be adjusted to your region.

Answer 2

This is an old post, but I figured it might help someone. Setting .setTimeStamp should do the trick.

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 ")
.setTimestamp("stDate", frmDate)
.setTimestamp("edDate", enDate)
.list();