Truncate DateTime in NHibernate QueryOver SelectGroup

Question

I have a pretty simple QueryOver query containing the following:

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

This query works as expected, but now I have a requirement to group by truncated date, as some of the Dates for these objects may contain a time component. This seems to be a trivial change, but I cannot find a way that NHibernate supports.

The obvious solution would be to change below, but it is not supported.

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

Any ideas?

thanks

Answer 1

Your QueryOver might 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!

Answer 2

You might want to add an auxiliary property to your map using the Formula command to be able to use the date (instead of date and time) in your queries.

here is an example from my code; it uses a decimal value, but this works fine with any subquery:

the model class has this property, which must be mapped to the formula:

 public virtual decimal Profit { get { return this.SellPrice - this.Cost; } set { return; } } 

fluentNHibernate map:

 //SellPrice and Cost are columns in the object table Map(v => v.Profit).Formula("(SellPrice - Cost)"); // this field is calculated, not read 

be sure to put the formula between the brackets ().

If you make a select formula that associates date and time with a date, you can group this property in your query.