How to use MySql date_add in Nhibernate?

Question

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>

Answer 1

Can you post the whole NHibernate query?

UPDATE: Well, the query is obviously malformed and erroneous:

Select distinct D from MBIgnition.Core.Domain.Model.Deal D where 1=1 and 
( (D.MortgageStatus = 30 ) or 
  (D.MortgageStatus = 35 ) or 
  (D.MortgageStatus = 40 ) or 
  (D.MortgageStatus = 45 ) or 
  (D.MortgageStatus = 55 ) or 
  (D.MortgageStatus = 50 ) ) and 
  // next line is erroneous as the first AND operator does not have a lefthand side operand
(( and ( date_add_interval(D.ApprovalDate, 1, YEAR) < current_timestamp() < date_add_interval(D.RenewalDate, -1, YEAR) ) ) )

As you can see, there's an AND operator in your code without any lefthand-side arguments. There should be something wrong with your HQL. Double check it again and if you couldn't pinpoint the error it will be useful to post the HQL or the criteria building code here.