Hibernate date mapping problem

Question

I am using a SQL Server database and Hibernate. I have the following problem related to storing dates:

I have the following code:

String hql = "select user from User user where user.createDate = :date";
Query query = HibernateUtil.getSession().createQuery(hql);
Date date2 = DateHelper.getEndOfDay(new Date());//this returns 2010-07-27 23:59:59.999
query.setParameter("date", date2);

When I am comparing the date 2010-07-27 23:59:59.999 I am getting results that have a date: 2010-07-28 00:00:00.0. Using the setDate() instead of setParameter() returns the users whose saved dates are on the same day, meaning that the hours, minutes etc. are discarded.

The hibernate maps the dates to java.util.Date but in the result list everything is Timestamp I guess because Timestamp extends java.util.Date. What would you suggest to do in order to have a good result when comparing for equality?

Answer 1

Did you try Query.setDate() or Query.setTimestamp() ?

Answer 2

If possible, I suggest you use the JODA DateTime API. Persist the DateTime object to the database using the PersistentDateTime Hibernate User Type class from the joda-hibernate project.

Answer 3

I think the problem is with MSSQL database since it saves the dates with a 3 millisecond precision as seen in one of the answers of this post.

So the problem is that since the time 23:59:59.999 can not be saved in MSSQL (probably with 3 millisecond precision you can never get 999 when comparing to times retrieved from the database this time has to be converted, and when converting it to some MSSQL compatible date it ends up to be the next day.

I think this is the real problem that I am seeing here. In order to avoid this I think I should format the dates I am saving.