Geek Answers Handbook

Compare only date component in HQL

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

Which operator can I use?

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

If you don't mind the functions specific to the database, you can do it (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 OracleDialect registers truncas StandardSQLFunction, and Oracle truncremoves the time component of the date.

+8
source

If you do not take into account database-specific functions (and you should), then work with two dates:

  • from date = date ,
  • date = , 23: 59: 59.999

>= <=

+9
"Select col from Table t where t.date = CURRENT_DATE()"

.

0

More articles:


All Articles