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?

"Or try to convert date java object to dateTime which I think might not be possible" - Look at java.sql.Date — Mr Moose
– Mr Moose, Commented Nov 23, 2011 at 8:15

Volo · Accepted Answer · 2011-11-23 11:04:39Z

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.

answered Nov 23, 2011 at 11:04

Volo

29.5k12 gold badges100 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Best Over a year ago

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 Over a year ago

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); }

Volo Over a year ago

@Best Weird… This should work just fine. Can you pls enable logging of generated SQL queries (see stackoverflow.com/a/2333173/648313 for details) to see what's happening there?

Collectives™ on Stack Overflow

Compare Date Java to DateTime Column in database table using SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related