1. Home
  2. Forum
  3. jQuery
  4. Date range filter on column

Date range filter on column

I am trying to filter my grid column CreatedOn based on the current date. To do this I am trying to combine two filters for greaterthanorequalto todays date at 00:00 and lessthan tomorrows date at 00:00 but I cannot get the filters to apply at the same time. Below is some sample code that I have tried with no success:

$("#grdJobs").ejGrid("filterColumn", [{
field: "CreatedOn",
operator: "greaterthanorequal",
value: "Mon Nov 13 2017 00:00:00 GMT+0100 (GMT Daylight Time)",
predicate: "and",
matchcase: false
}, {
field: "CreatedOn",
operator: "lessthan",
value: "Tue Nov 14 2017 00:00:00 GMT+0100 (GMT Daylight Time)",
predicate: "and",
matchcase: false
}
]);

Also is it possible to use a simple date string such as 13/11/2017 00:00 instead of the long winded date format shown above?



7 Replies
VN Vignesh Natarajan Syncfusion Team November 14, 2017 12:41 PM UTC

Hi Lewis, 

Thanks for using Syncfusion products. 

We have analyzed your query and we suspect that you want filter the date Column based on the two values. We achieved your requirement through filterSettitngs property of the Grid. Because in filterColumn method if you give two different filters to same column, grid will be filter based on the condition given at the last.  
So please refer the below code snippet 

$(function () { 
       $("#Grid").ejGrid({ 
            dataSource: Data, 
             allowPaging: true, 
             allowFiltering: true, 
             filterSettings:{  filteredColumns: [{ field: "OrderDate", operator: "greaterthanorequal", value: new Date('2017,11,14 00:00:00'), predicate: "and", matchCase: true },{ field: "OrderDate", operator: "lessthan", value: new Date('2017,11,15 00:00:00'), predicate: "and", matchCase: true }] }   , 
        columns: [ 
                         { field: "OrderID", isPrimaryKey: true, headerText: "Order ID", textAlign: ej.TextAlign.Right, validationRules: { required: true, number: true }, width: 90 }, 
          
   
Refer the below screenshot for the output 

 

Note: Date value must be specified in form of object only, previously you have provided in the string format.  

For your convenience we have prepared a JSplayground sample. 


Refer our online documentation for you reference 


If we misunderstood your query please get back to us with more details 

Regards, 
Vignesh Natarajan 



LH Lewis Hamill January 16, 2018 10:44 AM UTC

Hi Vignesh,

Sorry for the delay. My question was a little misleading as I actually need to implement the filters after the grid has initially been data bound. I had tried code such as the following but as you had stated in your previous answer, this method doesn't allow two filters on the same column:

$(function () {
    var gridObj = $("#grdJobs").data("ejGrid");
    gridObj.filterColumn(
        [
            {
                field: "CreatedOn",
                operator: "greaterthanorequal",
                value: new Date('2018,01,08 00:00:00'),
                predicate: "and",
                matchCase: true
            }, {
                field: "CreatedOn",
                operator: "lessthan",
                value: new Date('2018,01,09 00:00:00'),
                predicate: "",
                matchCase: true
            }
        ]
    );
});

Do you have any methods that apply a between filter on the date column after the grid has been databound?

VN Vignesh Natarajan Syncfusion Team January 17, 2018 11:47 AM UTC

Hi Lewis, 

Based on your query, we suspect that you want to filter the column of same fields with different values in the filterColumn method. So, we suggest that to use filteredColumns API at initial filtering. In the filterColumn method we can use only simple query to filter. 

If you want to filter the column after the Grid rendered then we suggest you to pass the filteredColumns in the Grid model and call for the refreshContent() of ejGrid control. 

We have filter the OrderID column by using the external button click event. 

Refer the below code example. 

 
<div class="content-container-fluid"> 
        <div class="row"> 
            <div class="cols-sample-area"> 
<button type="button" onClick="refreshData()">FilterColumn</button> 
 
                <div id="Grid"></div> 
 
            </div> 
        </div> 
    </div> 
 
    <script type="text/javascript"> 
        $(function () { 
            $("#Grid").ejGrid({ 
                // the datasource "window.gridData" is referred from jsondata.min.js 
                dataSource: window.gridData, 
                           allowPaging: true, 
                 
                           allowFiltering: true, 
                           filterSettings:{filterType:"excel"}, 
                            
                columns: [ 
                              
                            --- 
                ] 
            }); 
        }); 
        function refreshData(args) { 
 
            var gridObj = $("#Grid").ejGrid('instance'); 
 
            gridObj.model.filterSettings.filteredColumns = [{ field: "OrderID", operator: "equal", predicate: "or", value: "10248" }, { field: "OrderID", operator: "equal", predicate: "or", value: "10249" }]; 
 
           gridObj.refreshContent(); 
        } 
    </script> 
 


We have prepared a simple JsPlayground sample in the following link. 


Refer the help documentation. 



Regards, 
Vignesh Natarajan 



LH Lewis Hamill January 19, 2018 01:56 PM UTC

Hi Vignesh,

I tried to follow your sample but it doesn't work when I apply it to a datetime column. I have prepared a JsPlayground sample at the following link based on your example for you to provide more insight into the issue:
 
Link: http://jsplayground.syncfusion.com/lgeehzim

VN Vignesh Natarajan Syncfusion Team January 22, 2018 12:40 PM UTC

Hi Lewis, 
 
Thanks for the update. 
 
We have analyzed your query and we suspect that you want to filter the OrderDate column based on the time. In the JSplayGround sample, we have referred dataSource (window.gridData) from jsonData.min JavaScript file. In that file OrderDate column is specified with values that equal to 00:00:00 hour. So when you try to filter “No Records To Display” is shown in the Grid. 
 
Now we have modified the DataSource of the Grid. now try to filter the Grid. it will work correctly. 
 
Refer the below code snippet 
 
   var Data = [ 
        { OrderID: '10248', CustomerID: 'VINET', EmployeeID:'1',  OrderDate: new Date("2017,11,14 00:00:00"), Employee:{Address:'India'},Freight:'33.38',ShipName:'Alfreds Futterkiste',ShipCountry:'Brazil'}, 
                   { OrderID: '10249', CustomerID: 'TOMSP', EmployeeID:'2', OrderDate: new Date("2017,11,14 01:00:00"),Employee:{Address:'USA'},Freight:'11.61',ShipName:'Ana Trujillo Emparedados y helados',ShipCountry:'France'}, 
                   { OrderID: '10250', CustomerID: 'HANAR', EmployeeID:'3', OrderDate: new Date("2017,11,14 02:00:00"),Employee:{Address:'UK'},Freight:'65.83',ShipName:'Antonio Moreno Taquería',ShipCountry:'Denmark'}, 
                   { OrderID: '10255', CustomerID: 'ANTON', EmployeeID:'3', OrderDate: new Date("2017,11,15 00:00:00"), Employee:{Address:'USA'},Freight:'18.33',ShipName:'Hanari Carnes',ShipCountry:'Britain'}, 
                   { OrderID: '10252', CustomerID: 'SUPRD', EmployeeID:'6', OrderDate: new Date("2017,11,13 00:00:00"), Employee:{Address:'Britain'},Freight:'58.17',ShipName:'Berglunds snabbköp',ShipCountry:'Austria'}, 
                   { OrderID: '10253', CustomerID: 'WELLI', EmployeeID:'9', OrderDate: new Date("2017,11,14 23:59:59"), Employee:{Address:'Japan'},Freight:'58.17',ShipName:'Vins et alcools Chevalier',ShipCountry:'Mexico'}, 
                                                    . 
                                                    . 
                                                    . 
       ]; 
        $(function () { 
            $("#Grid").ejGrid({ 
                dataSource: Data, 
                                                          allowPaging: true, 
                 
 
 
 
 
Please get back to us if have any concerns. 
 
Regards, 
Vignesh Natarajan 


LH Lewis Hamill January 22, 2018 03:04 PM UTC

I played with the new demo a little and after I changed the date time to show the 13/11/2017 00:00:00 to 14/11/2017 00:00:00, the grid also included a row with the date time 16/11/2017 00:00:00.

Regardless of that bug, for some reason I am unable to get any filtered rows correctly for my grid. Is it perhaps due to me mixing the ASP.Net and Javascript platforms?

I keep trying to post the markup but it keeps getting trimmed or telling me that there are illegal words.

VN Vignesh Natarajan Syncfusion Team January 24, 2018 12:23 PM UTC

Hi Lewis, 
 
Sorry for the inconvenience caused. 
 
Query1: “the grid also included a row with the date time 16/11/2017 00:00:00” 
 
We have analyzed your query and the issue raise from the matchcase property of the predicates in the filterSettings. 
Refer the below screenshot  

              gridObj.model.filterSettings.filteredColumns = [ 
          { field: "OrderDate", operator: "greaterthanorequal", predicate: "and", matchcase: true , value: startOfFilter },  
          { field: "OrderDate", operator: "lessthan", predicate: "and", matchcase: true , value: endOfFilter }]; 
                              gridObj.refreshContent(); 
 
Previously it was stated as matchCase. So kindly modify the code snippet as above. 
We have modified the JS playground sample 


Query2: “Regardless of that bug, for some reason I am unable to get any filtered rows correctly for my grid. Is it perhaps due to me mixing the ASP.Net and Javascript platforms? ” 

 Can you please share the following details to reproduce the issue at our end. 
 
1.       Share the full grid rendering code (JS and ASP.NET). 
2.       Share the screenshot of script error in console window (If any) 
3.       Are you using any adaptors? If so mention he adaptor type and also ensure to perform server side filtering action. 
4.       If possible try to share the issue reproducible sample 
 
Regards, 
Vignesh Natarajan 
 


Loader.
Up arrow icon