How to perform date operations in hibernate HQL

Question

Hello friends,

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.

Does anyone know about it?

is there any tutorial available for this?

Please help if you can.

Thanks.

Answer 1

See http://stackoverflow.com/questions/639522/performing-date-time-math-in-hql for an example.

To use custom sql you must wrote a own hibernate dialect and register:

registerFunction("weekday", new SQLFunctionTemplate(Hibernate.INTEGER, "to_char(?1,'D')") );

Answer 2

You need to create your own dialect. Something like the following:

public class MyDialect extends MySQLInnoDBDialect{
      public myDialect() {
      super();
      registerFunction("date_add_interval", new SQLFunctionTemplate(Hibernate.DATE, "date_add(?1, INTERVAL ?2 ?3)"));
      }
    }

Answer 3

This is an open issue in Hibernate. As of Hibernate 3.3 there is no standard way to handle date comparisons purely in HQL:

http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434