Date Compare in HQL without timestamp

11
votes

I have to compare two dates in hibernate hql query. I am using java.util.Date in my java bean and using timestamp as datatype in mysql database.

select t from Task t where t.modifiedDate > t.endDate;

Above query compares time with date. What should i do to compare date in above query without time.

2
mysqlhibernatedatedatetimehql

2 Answers

20
votes

See the Hibernate documentations for available date functions

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html

In section 14.10 notice this line

second(...), minute(...), hour(...), day(...), month(...), and year(...)

So this should work

... where year(t.endDate) > year(t.startDate) 
    and month(t.endDate) > month(t.startDate)
    and day(t.endDate) > day(t.startDate)

The solution reported as satisfying the question is

 ... where DATE(t.endDate) > (t.startDate)
0
votes

The proposed solution with the date() function does not seem to be pure SQL and does not work for me (using SQL Server). The original solution (comparing day, month and year individually) is a step in the right direction, but cannot be done in this way, as, e.g., for a date to be in the future, a day need not be greater if the month is greater, etc. The following SQL, however, should work:

( year(t.endDate) > year(t.startDate) )
or 
( year(t.endDate) = year(t.startDate) 
  and 
  month(t.endDate) > month(t.startDate) )
or 
( year(t.endDate) = year(t.startDate) 
  and
  month(t.endDate) = month(t.startDate) 
  and
  day(t.endDate) > day(t.startDate) )