Filter by date crashes backend

[Karol Zynda]

Hi
My grid

<ejs-grid id="SendGrid" allowPaging="true" allowSorting="true" allowFiltering="true" toolbar="@(new List<string>() { "Search"})" childGrid="ChildGrid" height="630" width="auto" allowResizing="true">
   <e-data-manager url="/odata/orders" adaptor="ODataV4Adaptor" crossdomain="true"></e-data-manager>      
   <e-grid-pagesettings pageSize="15" pageSizes="@(new string[] {"5", "10", "15", "20", "All" })"></e-grid-pagesettings>    
   <e-grid-filtersettings type="Excel"></e-grid-filtersettings> 
   <e-grid-columns>
           ...
      <e-grid-column field="Quantity" headerText="Ilość paczek" width="150"></e-grid-column>
           <e-grid-column field="ImportDate" headerText="Data importu" width="150" type="date" format="dd/MM/yyyy"></e-grid-column> 
      <e-grid-column field="EditAddressDate" headerText="Data edycji adresu" width="150" type="date" format="dd/MM/yyyy"></e-grid-column>
      <e-grid-column field="State" headerText="Status" width="150"></e-grid-column> 
       </e-grid-columns>
   </ejs-grid>

And my odata controller

[EnableQuery]        
[HttpGet("Get")]
public IActionResult Get()
{
   return Ok(_apiDbContext.Orders.AsQueryable());
}    

Filtering by ImportDate or EditAddressDate causes problem with cast date time on ms sql server.
Query generated by grid:
/odata/orders/?$count=true&$filter=((ImportDate%20ge%202021-09-14T22:00:00.000Z)%20and%20(ImportDate%20le%202021-09-16T22:00:00.000Z))&$skip=0&$top=15
And error

SqlException: Conversion failed when converting date and/or time from character string.

When I manually remove time component from query, everything works. 
Column type is date so filter should send only date component. How to fix it? 

​

[Rajapandiyan Settu]

Hi Karol, 

 

Thanks for contacting Syncfusion support. 

 

By default, we have generated the QueryString in ODataV4 standards for ODataV4 Adaptor (i.e. JSON Stringyfied Date Object will be send in the QueryString). But in your requirement you don’t want to send the time value for date type column in the QueryString. 

 

Currently, we are checking the feasibility to achieve this at our end. So, we will update the further details on Sep 23^rd 2021. 

 

We appreciate your patience until then. 

 

Regards, 

Rajapandiyan S 

[Rajapandiyan Settu]

Hi Karol,  

  

Thanks for your patience. 

By default, we have generated the QueryString in ODataV4 standards for ODataV4 Adaptor (i.e. JSON Stringyfied Date Object will be send in the QueryString). But in your requirement you don’t want to send the time value for date type column in the QueryString.  

You can achieve this by using CustomAdaptor feature of Grid. In the processQuery method, you can customize the URL request as you want. 

Custom Adaptor: https://ej2.syncfusion.com/aspnetcore/documentation/grid/data-binding/#custom-adaptor 

[index.cshtml] 

<ejs-grid id="CustomAdaptor" created="created"> 

    ----- 

</ejs-grid> 

 

<script> 

    function created(args) { 

        class SerialNoAdaptor extends ej.data.ODataV4Adaptor { 

              processQuery() { 

                // Executing base class processQuery function 

                var original = super.processQuery.apply(this, arguments); 

                console.log(original); 

                // Here you can customize the request queries as per your need and send it 

                return original; 

              } 

        } 

        var grid = document.querySelector('#CustomAdaptor').ej2_instances[0]; 

        grid.dataSource = new ej.data.DataManager({ 

            url: 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/', 

            adaptor: new SerialNoAdaptor() 

        }); 

    } 

</script> 

Please get back to us if need further assistance with this. 

Regards,  

Rajapandiyan S