How to Add QBE Operators to Date column of table

The QBE “Query By Example” (as they are called by Oracle) table filters support the following operators.

Operator	Description
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
AND	And
OR	Or

So you can do things like this

>2000 and < 6000

L or A or F

This works for number and string fields only not for date fields.
This is how I implemented support for date fields.  The result looks like this



This example presumes you already have a table with filterable columns.
You should have a search region in your bindings executable list. Mine is called resolutionTargetQuery.  You will need to replace that value in the code below with whatever the name of your query is.
The first thing you need to do is add a calculated column to your view object that returns the numeric value of the date.   You need to get the java Date.getTime()  numeric value.   This will be the column used to filter the date on.   There are several ways to create this column.   If your date column is of type oracle.jbo.domain.Date then you can use an expression like shown below for your calculated column



After you have the new column, edit the table tree binding and shuttle the the new column  to the right to make it available for usage on the page.

Now we add a queryListener to the backing bean.  This will get called anytime you filter on the table.   In the queryListener we detect if the date field is being filtered and if so we switch it to filter on the numeric date column instead of the actual date column, pre-pending the value with the operator selected from the dropdown.  Remember, numeric columns support the operator but date columns do not.
Then we execute the query
After executing the query we need to put back the original date values so they don't  get removed from the table header bar.




In the jspx, on the af:table add queryListener property
 queryListener="#{pageFlowScope.backing_bean.processQuery}"

This gets called anytime you filter the table

In the Table Column definition add a toolbar to the filter facet containing the dropdown and date input field

 
<af:column align="center" filterable="true" headertext="Effective Start Date" sortable="true" sortproperty="targetEffectiveDate" width="130">
    <f:facet name="filter">
        <af:toolbar id="t1">
            <af:selectonechoice rendered="true" value="#{pageFlowScope.backing_bean.startDateOperator}">
                <af:selectitem label="=" value="">
                <af:selectitem label="&gt;" value="&gt;">
                <af:selectitem label="&gt;=" value="&gt;=">
                <af:selectitem label="&lt;" value="&lt;">
                <af:selectitem label="&lt;=" value="&lt;=">
            </af:selectitem></af:selectitem></af:selectitem></af:selectitem></af:selectitem></af:selectonechoice>
            <af:inputdate columns="8" value="#{vs.filterCriteria.targetEffectiveDate}">
        </af:inputdate></af:toolbar>
    </f:facet>
    <af:inputdate label=" " required="true" value="#{row.bindings.targetEffectiveDate.inputValue}">
        <af:convertdatetime pattern="#{bindings.resolutionTargets.hints.targetEffectiveDate.format}">
    </af:convertdatetime></af:inputdate>
</af:column>


                      

In the backing bean add your queryListener

// class properties with getters and setters 

 String startDateOperator="";  
 String endDateOperator=""; 

 private void processQuery(QueryEvent queryEvent) {
    
    try{
    RichTable table = (RichTable)queryEvent.getComponent();
    
    
    FilterableQueryDescriptor filterQD = (FilterableQueryDescriptor)table.getFilterModel();
    Map filterCriteria = filterQD.getFilterCriteria();
      
       
    
    Date start = (Date)filterCriteria.get("targetEffectiveDate");
    Date end = (Date)filterCriteria.get("targetEndDate");
    
    // get time from Date object and filter on different column
    // containing long value of date instead of Date
    if (start != null){
        long tm = start.getTime();
        filterCriteria.put("targetEffectiveDateLong",startDateOperator+tm);  // sorting on this column instead
        filterCriteria.put("targetEffectiveDate", null); // origianl column sort value must be null or will fail
    } else{
        filterCriteria.put("targetEffectiveDateLong", null);
        filterCriteria.put("targetEffectiveDate", null);
    }
    if (end != null){
        long tm = end.getTime();
        filterCriteria.put("targetEndDateLong", endDateOperator+tm); // sorting on this column instead
        filterCriteria.put("targetEndDate", null); // this must be null or will fail
    }else{
        filterCriteria.put("targetEndDateLong", null);
        filterCriteria.put("targetEndDate", null);
    }
       
    
            
    DCBindingContainer bc =  (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
    Object execBinding =  bc.findExecutableBinding("resolutionTargetsQuery");
      
 // if table editingMode mode propery is set to clickToEdit and
 // if you have a selected row when you filter the table and the filter removes the selected row
// you will get an exception when you select a new row because it can't find the previous selected row and will get
// JBO-25006: Value oracle.adf.model.bean.DCDataRow passed as parameter row to method ViewRowSetIteratorImpl.scrollRangeTo is invalid: not in the row set. 
// calling this function will unselect all selected rows prior to filtering to fix that problem.
     ADFUtils.resetRichTableSelection(table); // de-select any selected rows.


    // not suppose to cast to internal oracle class but other technique commented out below
    // doesn't work with POJO data control.  It may work work normal view object
    // execute filter
    ((FacesCtrlSearchBinding) execBinding).processQuery(queryEvent);
     
    
    // this will fail
    //           invokeQueryEventMethodExpression("#{bindings.allEmployeeesQuery.processQuery}",queryEvent);
    
    // restore original filter date values so they continues to display in the QBE bar
        filterCriteria.put("targetEffectiveDate", start); // restore original
        filterCriteria.put("targetEffectiveDateLong", null); // 
        filterCriteria.put("targetEndDate", end); // restore original
        filterCriteria.put("targetEndDateLong", null);
    
        
    } catch (Exception e){
          e.printStackTrace();
        }
    }

  With a little more work you could support a between operator.  This expression works on numbers
<100000 and >25000.  So add two date input fields and show the second one with a partial trigger when you select "between" from the dropdown.  Then in your query listener set the appropriate filter statement using the value of the two dates.

Here is a link to another implementation