How to use MySql date_add in Nhibernate?
- by jalchr
This really puzzled for hours, I searched all over the internet, but got no working solution. Can someone point where the problem is ... thanks !
I created my own dialect class
public class MySQLDialectExtended : MySQLDialect
{
public MySQLDialectExtended()
{
RegisterFunction("date_add_interval", new SQLFunctionTemplate(NHibernateUtil.Date, "date_add(?1, INTERVAL ?2 ?3)"));
}
}
Then I try to use it as follows:
query.Append(
" ( date_add_interval(D.ApprovalDate, 1, YEAR) < current_timestamp() < date_add_interval(D.RenewalDate, -1, YEAR) )");
It fails with following exception:
NHibernate.Hql.Ast.ANTLR.QuerySyntaxException : Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 677
where the column number is at the end of the first 'YEAR' word.
Edit:
here is my configuration
<property name="dialect">MyCompanyName.MySQLDialectExtended, MyCompanyName</property>
<property name="hbm2ddl.keywords">none</property>
