Menu
I have a table (in Oracle 9 and up) where I need to find all entries for a given day using Hibernate. The entries have timestamps (with data type 'date'). Some of the entries have a time, others only have a date. This can not be changed, as this is the output of other applications which I can not change. In SQL I would write something along the lines of
SELECT * FROM table WHERE trim(table.date) = to_date('11.06.2009')
to get all entries for the date I am looking for. I was wondering how I can get Hibernate to do this using HQL. I know I can use SQL-queries in Hibernate, but this seems to be less clean. Is there a way to do this? If possible, this way should also work on non-oracle databases where timestamp would be the data type.
887
YEAR, -1); String dateLimit = cal. getTime(). toString(); Date dateFormatter = new SimpleDateFormat("dd-MMM-yyyy").
In Oracle You Can Use HQL trunc() Date Function Another function you can use for HQL date comparison queries is the HQL trunc() function. This function discards the hour part of the date. Please note that this will only work when using Hibernate with Oracle and the OracleDialect .
Keywords like SELECT, FROM, and WHERE, etc., are not case sensitive, but properties like table and column names are case sensitive in HQL.
You could put two constraints into your HQL, one that specifies greater than or equal to the start of the date you are searching on, and one that specifies less than the next day.
i.e.
table.date >=11.06.2009 AND table.date < 11.07.2009
HQL (as well as SQL) also allows for:
table.date between 11.06.2009 AND 11.07.2009
Keep in mind between is always inclusive, meaning it's both >= and <= respectively.
More details on between here: http://www.coding-dude.com/wp/java/hibernate-java/hibernate-hql-between-expression/
86
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
