Compare Date Java to DateTime Column in database table using SQL

Question

I've got two Date Java objects which will be assigned to some variables in sql query (because I use Hibernate) in order to compare them with a DateTime type column to get rows with a specified time range, for example:

  WHERE event_date >= :startDate and event_date < :finishDate

I couldn't compare date to datetime directly so I thought of 2 possible solutions:

• Try to convert event_date field using a query to date field before the comparison.
• Or try to convert date java object to dateTime which I think might not be possible..

What would you suggest me to do?

Answer 1

I guess you have the problems because you use setDate (correct me if I'm wrong) and setDate method:

Binds the date (time is truncated) of a given Date object to a named query parameter.

Use setTimestamp instead, which binds the date and time of a given Date object:

java.util.Date startDate = … ;
java.util.Date finishDate = … ;
Query query = session.createQuery("from YourTable where event_date >= :startDate and event_date < :finishDate");
query.setTimestamp("startDate", startDate);
query.setTimestamp("finishDate", finishDate);

 
p.s.: be sure to use java.util.Date object and NOT the java.sql.Date one.