Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3.1
Name and version of the database you are using: Sql Server

I need to obtain sum of two date columns (using group by) as the number of days.

In HQL i would use this:



Hoverver I cannot obtain the difference between two dates using
SUM(period.endDate-period.startDate)... because of using SqlServer i would use native query (via session.createSqlQuery()) using the SqlServer native date calculation functions:

SUM( DATEDIFF( Day, start_date, end_date ))

but unfortunaltelly i'm not able to produce correct code.. Can anyone lend me a hand and show how to rewrite this using native sql query?

You can execute native SQL Queries using the createSQLQuery Method from the Criteria class.
Its like the PreparedStatement using the ? as a placeholder.

Pseudo code:
SQLQuery sqlQuery = session.createSQLQuery("select ... from ... where x=? and y=?");
sqlQuery.setDate(0, ...);
sqlQuery.setDate(1, ...);
List result = sqlQuery.list();

The objects returned in the result depend on the Columns, iirc a single column is returned as a single appropriate object while more columns are returned as an array of objects in the result.

<Don't forget to rate if this helped you>