Compare only date component in HQL

Question

I need to select some entities using HQL filtering by time. But I need to compare only date component without time. For example 1.1.2011 12.00 and 1.1.2011 13.20 must be equal.

What operator can I use?

@Query("FROM Item item " +
  "WHERE item.date [equality by date operator] :date " )
List<Item> findByDate(@Param("date") Date date);

Answer 1

If you do not mind DB-specific functions, you'd be able to do this (using Oracle as an example).

@Query("FROM Item item WHERE trunc(item.date) = trunc(:date) " )
List<Item> findByDate(@Param("date") Date date);

You can use this because the OracleDialect registers trunc as a StandardSQLFunction and Oracle's TRUNC removes the time component of a date.

Answer 2

If you do mind the DB-specific functions (and you should) then work with 2 dates:

1. from date = date with time set to midnight
2. to date = date with time set to 23:59:59.999

then query for dates >= from date and <= to date