I have to write something like:
SELECT * FROM foo WHERE HOUR(field) = 10
Where field is datetime.
I want to ignore the dates - for statistics purpose, I need to extract only events, that occurs between 10:00 and 10:59.
I know, I can:
String sql = "HOUR(CREATED_AT) = 10";
criteria.add(Expression.sql(sql));
But I'd like to use the hibernate mechanisms, rather than SQL strings.
Is there any way to do this?
Simple solution - should work like this:
var hourProjection = Projections
.SqlProjection(" DATEPART(HOUR,field) as hour " // the left side of the expression
, new[] {"hour"} // alias
, new IType[] {NHibernateUtil.Int32}); // type is int
criteria.Add(Expression.Eq(hourProjection, myValue)); // myValue = 10
Check: How to compare datepart Month using Nhibernate Criteria?
NHibernate-ish solution - in case we would like to use that function HOUR extensively, we can extend the Dialect with its definition:
public class CustomMsSql2012Dialect : MsSql2012Dialect
{
public CustomMsSql2012Dialect()
{
RegisterFunction("HOUR",
new SQLFunctionTemplate(NHibernateUtil.Class, "DATEPART(HOUR,?1)"));
}
}
And inject that into configuration:
<property name="dialect">MyLib.CustomMsSql2012Dialect,MyLib</property>
And consume it like this:
var hourFunctionProjection = Projections
.SqlFunction("HOUR", NHibernateUtil.Int32, Projections.Property("Field"));
restrictions.Add(Restrictions.Eq(hourFunctionProjection, 10));
Check: Using SQL CONVERT function through nHibernate Criterion
This get Hours from datetime
select DATEPART(HOUR, field) from tableName
