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.

1 COMMENT
ad

[…] This post was mentioned on Twitter by Garry Shutler, Andrew Bullock. Andrew Bullock said: blogged: NHibernate and Dates http://blog.muonlab.com/2009/12/14/how-to-get-at-the-date-part-of-a-datetime-with-hql-in-nhibernate/ […]

Post a comment