Search Oracle date type column with hibernate, seach by everything in that day - ignore the time.

Question

hi there

<property name="batchCreatedDate" type="java.util.Date">
  <meta attribute="field-description">batch create date</meta>
  <column name="BATCH_CREATED_DATE" length="7" not-null="true" />
</property>

table column type is

BATCH_CREATED_DATE  DATE            NOT NULL

With the data in that date column being similar to this '2010-05-13 14:56:36.0'

now I want to search for all items within the 24 hours of 2010-05-13, currently my call only returns all items with date "2010-05-13 14:56:36.0" exactly.

What would my HQL statement look like to hand this kind of scenario?

Thank you so much

Answer 1

This could be useful. http://stackoverflow.com/questions/980366/using-hql-to-query-on-a-date-while-ignoring-the-time-on-oracle

Answer 2

Depending on the parameters you're going to pass, I can think of several solutions:

• With '2010-05-13 00:00:00.0' and '2010-05-13 23:59:59.9':

  from MyEntity e where e.date between :startDate and :endDate 
  

• With '2010-05-13 00:00:00.0' and '2010-05-14 00:00:00.0':

  from MyEntity e where :startDate <= e.date and e.date < :endDate
  

• With '2010-05-13 XX:XX:XX.X':

  from MyEntity e where year(e.date)  = year(:aDate) 
                    and month(e.date) = month(:aDate)
                    and day(e.date)   = day(:aDate)