Using HQL to query on a date while ignoring the time on Oracle

hibernate date timestamp hql
Thomas Lötzer picture Thomas Lötzer · Jun 11, 2009 · Viewed 67.5k times · Source

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.

Answer

tschaible picture tschaible · Jun 11, 2009

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/

Related questions

How to perform date operations in hibernate

I want to perform data time operations using hibernate HQL. I want to add and subtract two dates as well as I want to subtract 1 year or 1 month from a particular date. How is this possible using HQL in hibernate?

Read More
How to use the Java 8 LocalDateTime with JPA and Hibernate

I have the following class description snippet: ... @Column(name = "invalidate_token_date") @Temporal(TemporalType.TIMESTAMP) private LocalDateTime invalidateTokenDate; .... This code doesn't work on Hibernate 4 because @Temporal doesn't support LocalDateTime. I saw the suggestion on how to use LocalDateTime from Joda-Time …

Read More
Cannot convert '0000-00-00 00:00:00' to TIMESTAMP

the field definition /** Date. */ @Column(columnDefinition = "datetime") private Date date; setter public void setDate(final Date date) { DateFormat dfmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { this.date = dfmt.parse(dfmt.format(date)); } catch (ParseException e) { // TODO Auto-generated catch block …

Read More