Truncate DateTime in NHibernate QueryOver SelectGroup

Question

I have a fairly run-of-the-mill QueryOver query containing the following,

.SelectList(list => list
    .SelectGroup(() => txn.GroupField)
    .SelectGroup(() => txn.Date))
.List<object[]>()

This query works as expected however I now have a requirement to group by the truncated Date as some of the Date's for these objects may contain a time component. This seems like it should be a trivial change but I can't find a way that is supported by NHibernate.

The obvious solution would be the change below but is not supported.

.SelectGroup(() => txn.Date.Date))

Any ideas?

Thanks

Answer 1

Your QueryOver could look like this:

Status alias = null;
var query = QueryOver.Of(() => alias)
    .Select(Projections.GroupProperty(
        Projections.SqlFunction("date", NHibernateUtil.Date, Projections.Property(() => alias.Date))));

Output (pseudo sql):

SELECT 
           ...
FROM       [Status] this_
GROUP BY   dateadd(dd, 0, datediff(dd, 0, this_.Date))

Hope this helps, cheers!