Using Year/Month/Day in HQL query

sir-archimedes · **Joined:** Mon Mar 20, 2006 7:59 am **Posts:** 30

Hello,

I am using NHibernate 1.2.0 Alpha 1/Mssql2005-Dialect and encountered problems using the year method in a HQL query. I tried to query this:

Code:

SELECT year(t.CreationDate) FROM Task t JOIN t.Actions act group by year(t.CreationDate), month(t.CreationDate)

That did not work. I received this error message:

Quote:

undefined alias or unknown mapping: year [SELECT year(t.CreationDate) FROM AMS.Core.Domain.Task t JOIN t.Actions act group by year(t.CreationDate), month(t.CreationDate)]

Querying this works properly:

Code:

SELECT count(t) FROM Task t JOIN t.Actions act group by year(t.CreationDate), month(t.CreationDate)

Is there a possibility to do such grouping queries database independent with NHibernate or do I have to query each and every row and do the aggregation on my own? I think I would gain a lot of performance if I could let the database aggregate the data.

I hope anybody can help me. Thanks and regards,
Dominik

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

That's simply not possible. Check your SQL server's docs: you cannot use functions, aggregates, subselects or anything other than simple columns in group bys. It's not an hibernate restriction, just your DBMS'.

Either use group by whole dates, or do the grouping yourself, after you've loaded the ungrouped list.

sir-archimedes · **Joined:** Mon Mar 20, 2006 7:59 am **Posts:** 30

That is not true: I can group by the year and the month. That also works well with NHibernate - at least with MSSQL in the backend. But I cannot select year(...) as a column.

Therefore I was asking how to select also the year(...) as column. It is quite useless when I get all the data aggregated by year/month if I don't know which data is for which month.