問題描述
NHibernate：HQL：從日期字段中刪除時間部分 (NHibernate:HQL: Remove time part from date field)
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.
參考解法
方法 1:
You could use one of the built in functions in hql see mssql2000dialect
In your case Date seems to be the one
方法 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.