Hi all,

I have a problem with date field. I searched topics here and couldn't find the answer,
I also posted a question on Stack Overflow, but still got no specific answer.

The problem is following:

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

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:

...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 in HQL, but I got this error message.

I got answer on Stack Overflow that I should use mssql2000 dialect.
I'm using mssql2005 dialect, but I'm guessing that I can apply built in function AFTER I get the result from DB,
or on some value that I will PASS to HQL query, which is not what I need here.
I need to remove time part from date field, WHILE the GROUP BY is performed,
so that my final outcome is records grouped by date only, not date-time.

Thanks in advance,
Dejan.

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

And inside the XML definition I placed this:

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

and this works flawlessly.