How to Ignore Time Component of Date column when filtering on Date.

When you create a table that supports column filtering,  the default behavior for filtering on dates is to compare the date directly.  This means that if the date in the database has a time component the row will not match and no results will be found.    If you don't need to display the time along with the date in the table then you can just put a trunc(...) around the date in the original query.   If you do need to display the time then you can add another view attribute to your query that outputs the trunc'd version of the date.   The new column will not be displayed but will be used for sorting and filtering.   The af:column component has a sortProperty attribute that usually contains the column attribute name for the column displayed below it.   This does not have to be the case however.  The sortProperty can be set to any column in the view object so that when you put a value in the filter field it filters and sorts on a different column then the one displayed below it.  Set the sortProperty for the date/time column to the date only column and you will get the desired behavior.  When you filter on a date it will return all rows with that date regardless of the time portion of the date.

There is another way to accomplish this that will make every Date filter within your application time agnostic so that developers do not need to add this extra column every time they want to filter on the date portion of a date/time column.To do this you need to create a custom SQL builder class that extends OracleSQLBuilderImpl and override the getFormattedLHSCompareFragment method (Left Hand Side Compare Fragment)  This is called whenever filtering is performed.   In this method add the trunc(...) around the value.  Sample code is shown below.


This class is activated by adding a JVM parameter on startup (jbo.SQLBuilder)


  -Djbo.SQLBuilder=com.yourCompany.model.custom.MySQLBuilderImpl

/**
 * Override method in OracleSQLBuilderImpl in order to trunc (remove time component)
 * when filtering on Dates in tables (Query By Example) QBE
 * This class is activated by adding a JVM parameter
 *  -Djbo.SQLBuilder=com.yourCompany.model.custom.MySQLBuilderImpl
 */
public class MySQLBuilderImpl extends OracleSQLBuilderImpl {
    
    private static final Log log = LogFactory.getLog(MySQLBuilderImpl.class);
    private static SQLBuilder mSQLBuilderInterface=null;  
    
    public MySQLBuilderImpl() {
    }


  /**
   * Gets the singleton instance of this class.
   * @return a SQLBuilder object.
   */
  synchronized public static SQLBuilder getInterface()
  {
     if (mSQLBuilderInterface == null)
     {
        if (Diagnostic.isOn())
        {
           Diagnostic.println("OracleSQLBuilder reached getInterface");
        }

        mSQLBuilderInterface = (SQLBuilder)(new MySQLBuilderImpl());

        if (Diagnostic.isOn())
        {
           Diagnostic.println(mSQLBuilderInterface.getVersion());
        }
     }
     return mSQLBuilderInterface;
  }
 

  @Override
  public String getFormattedLHSCompareFragment(ViewObject vo, ViewCriteria vc,
                                               AttributeDef attrDef,
                                               int sqltype, String lhs,
                                               Object rhs) {
   String value =   super.getFormattedLHSCompareFragment(vo, vc, attrDef, sqltype, lhs, rhs);
   
    if (sqltype==Types.DATE || sqltype==Types.TIMESTAMP) {
      
      // only apply trunc to database columns, not view alias names used for in-memory filtering
      // ADF does not handle a trunc for a in-memory filter and will get an error
      Integer attr = vo.getAttributeIndexOf(value); // 
      if (attr == -1){  // not found, meaning it's a database column name, not an attribute name.
      
       value = "TRUNC("+value+")";
        if (log.isDebugEnabled()) {
            log.debug("MySQLBuilderImpl truncating date value:"+value);
        }
      }
    }
    return value;
  }
}