5

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?

Volo
  • 28,673
  • 12
  • 97
  • 125
Best
  • 2,200
  • 9
  • 30
  • 49
  • 1
    "Or try to convert date java object to dateTime which I think might not be possible" - Look at java.sql.Date – Mr Moose Nov 23 '11 at 08:15

1 Answers1

4

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.

Volo
  • 28,673
  • 12
  • 97
  • 125
  • Thanks for your reply and you were right about me using setDate. I tried this method but the returned results still did not seem to be filtered. – Best Nov 24 '11 at 08:33
  • yes I do use java.util.Date. This is an example how the final date is created with the current time: if(fDate != null){ // adjust final date Calendar c = Calendar.getInstance(); c.setTime(fDate); c.add(Calendar.DAY_OF_YEAR, 1); Date fDate2 = c.getTime(); q.setDate("fdate", fDate2); } – Best Nov 24 '11 at 08:50
  • @Best Weird… This should work just fine. Can you pls enable logging of generated SQL queries (see http://stackoverflow.com/a/2333173/648313 for details) to see what's happening there? – Volo Nov 28 '11 at 15:45