muonlab » NHibernate

random .NET and web development musings

If you need to get access to the date part of a DateTime through HQL, the easiest way is to use a custom dialect:

public class CustomDialect : SQLiteDialect
{
	public CustomDialect()
	{
		RegisterFunction("DateOnly", new StandardSQLFunction("date", NHibernateUtil.String));
	}
}

public class CustomDialect : MsSql2005Dialect
{
	public CustomDialect()
	{
		RegisterFunction("DateOnly", new SQLFunctionTemplate(NHibernateUtil.DateTime, "DATEADD(dd, 0, DATEDIFF(dd, 0, ?1))"));
	}
}

Because I use SQLite for my integration tests and MSSQL2005 for my runtime database, I need two different dialects, one for each provider. Using the custom function name “DateOnly” I can have the same HQL work on both databases 😀

You can then use this function, like so:

select DateOnly(x.Date), count(x.Id)
from someEntity x
group by DateOnly(x.Date)

Win.