Unable to filter a (DateTime) column...

[Martin]

Using Syncfusion.blazor 18.4.0.31 

I have created a grid which is being provided with data from an SQL select. Two of the columns are DateTime format and I and to use a DateTime calendar component as the filter picker. For example the "Start Time" column should be filtered the selected DateTime  with the operator of GreaterThanOrEqual.

 Despite trying different formats, every time a filter is applied to the column no records are show. I have experimented by using fabricated DateTime values to filter the column rather than relying on the change value of the picker, but even manually entering a DateTime I can not get any results to display.

The code I am using is below... (I am in the UK and so our DateTime format is dd/MM/yyyy hh:mm:ss)

<SfGrid @ref="Grid" DataSource="@GridData" AllowFiltering="true" AllowPaging="true" AllowSorting="true" AllowResizing="true">

                <GridPageSettings PageCount="5"></GridPageSettings>

                <GridColumns>

                   

                    <GridColumn Field=@nameof(CDRmodel.StartTime) HeaderText="Start Time" Width="180" TextAlign="TextAlign.Right" AllowSorting="false" CustomFormat="@(new { type="DateTime", format= "dd/MM/yyyy hh:mm:ss" })">

                        <FilterTemplate>

                            <SfDateTimePicker TValue="DateTime?" Placeholder='Select a date and time'>

                                <DateTimePickerEvents TValue="DateTime?" ValueChange="StartDateChange"></DateTimePickerEvents>

                            </SfDateTimePicker>

                        </FilterTemplate>

                    </GridColumn>

                    <GridColumn Field=@nameof(CDRmodel.EndTime) HeaderText="End Time" Width="180" TextAlign="TextAlign.Center" AllowSorting="false"  CustomFormat="@(new { type="DateTime", format= "dd/MM/yyyy hh:mm:ss" })"> </GridColumn>

                    <GridColumn Field=@nameof(CDRmodel.OriginationNumber) HeaderText="Origination No." Width="140" AllowSorting="false"></GridColumn>

                    <GridColumn Field=@nameof(CDRmodel.OriginationName) HeaderText="Origination Name" TextAlign="TextAlign.Right" Width="120" AllowSorting="false"></GridColumn>

                    <GridColumn Field=@nameof(CDRmodel.CalledNumber) HeaderText="Called No." Width="140" AllowSorting="false"></GridColumn>

                    <GridColumn Field=@nameof(CDRmodel.CalledName) HeaderText="Called Name" TextAlign="TextAlign.Right" Width="120" AllowSorting="false"></GridColumn>

                </GridColumns>

            </SfGrid>

@code{

    public void StartDateChange(ChangedEventArgs<DateTime?> args)

    {

        Grid.FilterByColumn("StartTime", "GreaterThanOrEqual", args.Value);

    }

}

Using the custom format on the field appears to make no difference.

I have searched long and hard and cannot find any solution to this, so any help would be appreciated.

Thanks in advance - Martin

[Renjith Singh Rajendran]

Hi Martin, 

Greetings from Syncfusion support. 

We suggest you to ensure to provide proper casing for the operator name to overcome the reported problem you are facing. Please refer and use as like the code below, 

 

public void StartDateChange(ChangedEventArgs<DateTime?> args) 

{ 

    Grid.FilterByColumn("StartTime", "greaterthanorequal", args.Value); 

} 

And also we have deprecated CustomFormat property of GridColumn in our latest versions. You can apply custom format to Date Columns by providing your needed format using the Format property of GridColumn based on C# format, as like the below code. 

<GridColumn Field=@nameof(CDRmodel.StartTime) HeaderText="Start Time" Width="180" TextAlign="TextAlign.Right" AllowSorting="false" Format="dd/MM/yyyy hh:mm:ss">    <FilterTemplate>        <SfDateTimePicker TValue="DateTime?" Placeholder='Select a date and time' Format="dd/MM/yyyy hh:mm:ss">            <DateTimePickerEvents TValue="DateTime?" ValueChange="StartDateChange"></DateTimePickerEvents>        </SfDateTimePicker>    </FilterTemplate></GridColumn>

Please get back to us if you need further assistance. 

Regards, 

Renjith Singh Rajendran 

[Serghei]

In your default samples that get installed from your new project template and that you use in your docs, if I enter the exact date in the filter bar for the Order Date then I get that record correctly. However, if I change the type of the OrderDate property from DateTime? to DateTime, and then do the same, the same filtering by date stops working, and returns no records.

Also, if I manually set the operator to use for that column, as per your docs, like shown below

<GridColumn Field=@nameof(Order.OrderDate) FilterSettings="@(new FilterSettings {Operator=Operator.GreaterThanOrEqual})"...

it does not take effect, and still treats it as an Equals operator when you use the default DateTime? or returns no records if you change it to DateTime.

[Martin]

Hi,

   Thank you and I can confirm that this resolves my issue.

However, it may be a good idea to have a word with whoever is responsible for the Syncfusion online documentation and examples, as I had used that capitalisation based on what I found on the page at https://blazor.syncfusion.com/documentation/datagrid/filtering/ . On that page both in the example code and in the table of Operators the Operators are shown in camel case.

Best regards and thank you again - Martin

[Martin]

Hi Serghei,

   What I have found from the answer provided to me by Renjith  is that the case of the Operator matters. Like you I have copied and pasted from the Syncfusion documentation, but it appears that this is wrong and the operators should be all lowercase. When I amended my example it then worked as expected..

Regards - Martin

[Renjith Singh Rajendran]

Hi Serghei/Martin, 

Thanks for your updates. 

Query 1 : FilterSettings="@(new FilterSettings {Operator=Operator.GreaterThanOrEqual})"...it does not take effect, and still treats it as an Equals operator when you use the default DateTime? 

We have confirmed this as a bug and logged a defect report “Custom filter operator for Date column is not applied during Filtering” for the same. Thank you for taking the time to report this issue and helping us improve our product. At Syncfusion, we are committed to fixing all validated defects (subject to technological feasibility and Product Development Life Cycle) and including the defect fix in our upcoming bi-weekly release which is expected to be rolled out by the end of January 2021.  

    

You can now track the current status of your request, review the proposed resolution timeline, and contact us for any further inquiries through this link.     

https://www.syncfusion.com/feedback/20972/custom-filter-operator-for-date-column-is-not-applied-during-filtering 

Query 2 : if I change the type of the OrderDate property from DateTime? to DateTime, and then do the same, the same filtering by date stops working, and returns no records. 

We tried reproduce the reported scenario by creating a sample with the codes from the below documentation, but we could not face the reported problem when having DateTime as type for OrderDate column. We are attaching the sample for your reference, please download the sample from the link below, 

Sample : https://www.syncfusion.com/downloads/support/directtrac/general/ze/ServerApp1031759898 

Documentation : https://blazor.syncfusion.com/documentation/datagrid/filtering/#filtering 

Please refer the above sample and if you are still facing this reported problem, then kindly get back to us with the following details for better assistance. 

1. Share the sample which you have tried from your side, or share with us a simple issue reproducing sample.
2. Or if possible reproduce the problem with the above attached sample and share with us for further analysis.
3. Share with us the exact scenario or proper replication procedure.

Query 3 : On that page both in the example code and in the table of Operators the Operators are shown in camel case. Like you I have copied and pasted from the Syncfusion documentation, but it appears that this is wrong and the operators should be all lowercase. 

When using the Default operator change feature by using GridColumn’s FilterSettings property as like the below documentation, then it is suggested to use Camel case letters for Operator value. 

Camel case “Contains” operator is used : https://blazor.syncfusion.com/documentation/datagrid/filtering/#change-default-filter-operator 

And when using the operator value as argument for FilterByColumn method as like your(Martin) case, then it is suggested to use lower case letters for Operator value as like the FilterByColumn method code in below documentation, 

Lower case “contains” operator is used : https://blazor.syncfusion.com/documentation/datagrid/filtering/#filter-bar-template-with-custom-component 

Please get back to us if you need further assistance. 

Regards, 

Renjith R 

[Serghei]

Martin - glad that it worked for you. I guess the issues I found are different, yet still related to filtering date columns.  Sorry to highjack your issue.

Renjith - please see the attached sample project, which I just created from your new project wizard, and changed the DateTime? to be non-nullable for the OrderDate. If you enter yesterday's date in the filter bar, it will return no results. If you change it back to DateTime?, it will return one record.

The issue goes away if you populate the dates without the time, e.g. OrderDate = DateTime.Today.AddDays(-x),

but it still doesn't look right when the filtering works differently on nullable and non-nullable DateTime columns that have time set.

Attachment: SyncfusionBlazorApp2_6a4ef9cf.zip

[Renjith Singh Rajendran]

Hi Serghei, 

Thanks for your update. 

We would like to inform you that, using the DateTime.Now will bind the current time value also to Grid, so at these case Grid data contains time also. So, as OrderDate is a Date column but has Data with time, then the filtering won’t apply properly as no time information will be provided in filter bar. As on filtering you will only be providing date value, no value matches the data in that date column(OrderDate). 

So, we suggest you to have the value for the Date column(OrderDate) in your DataSource as like the below documentation code to perform filtering for the Date column in Grid. 

Documentation : https://blazor.syncfusion.com/documentation/datagrid/filtering/#filtering 

 

Please get back to us if you need further assistance. 

Regards, 

Renjith Singh Rajendran