NHibernate:HQL: Remove time part from date field

Question

I'm trying to group table values by date, and this HQL query works fine:

 SELECT  af.SubmitedDate, COUNT (af.Id) 
 FROM ApplicationForm af 
 GROUP BY af.SubmitedDate

The problem is that field af.SubmitedDate also contains time part, sine I'm using SQL Server 2005, so the grouping is done by date-time, not only by date. When I try to do something like this in HQL:

SELECT CONVERT(VARCHAR(10), af.SubmitedDate, 105), COUNT (af.Id)
FROM ApplicationForm af 
GROUP BY CONVERT(VARCHAR(10), af.SubmitedDate, 105)

...I receive this error:

NHibernate.QueryException was unhandled by user code
Message="undefined alias or unknown mapping: CONVERT

This query is correct in TSQL and I even read somewhere that CONVERT can be used, but I read it on Java's Hibernate forum.

So, how can I remove time part from this date, so that grouping works correct ?

Thanks in advance, Dejan.

Answer 1

Hi!

You could use one of the built in functions in hql see mssql2000dialect

In your case Date seems to be the one

Answer 2

I created a Scalar-Valued function inside the SQL Server 2005, which encapsulates this command:

CONVERT(VARCHAR(10), af.SubmitedDate, 105)

And inside the XML definition I placed this:

<property name='SubmitedDateWithoutTime' formula='dbo.RemoveTimeFromDateTime(SubmitedDate)'/>

So, my HQL query looks at the end like this:

SELECT  af.SubmitedDateWithoutTime, COUNT (af.Id) 
FROM ApplicationForm af 
GROUP BY af.SubmitedDateWithoutTime

and this works flawlessly.