Filtering on DateTime data type

[Craig]

I"m trying to filter a grid that is bound to many fields including a few that are DateTime. My filter works great until I try to do one on a datetime type field then it errors out. What operator can I use on a datetime? Here's the error and the settings for the filter.

Message"The binary operator GreaterThanOrEqual is not defined for the types 'System.DateTime' and 'System.String'."string

$("#Grid").data("kendoGrid").dataSource.filter({
            logic: "or",
            filters: [
                {
                    field: "DateEntered",
                    operator: "gte",
                    value: searchValue
                },
                {
                    field: "DateEntered",
                    operator: "lte",
                    value: searchValue
                },

Thanks!

Craig

[Georgi]

Hello Craig,

Thank you for the provided code. Both gte and lte are valid operators for Date columns. I created a small sample in order to replicate the issue but filtering works as expected on my end.

Attached you will find the sample, could you please examine it and let me know what I am missing?

I look forward to your reply.


Regards,
Georgi
Progress Telerik

[Craig]

Thank very much for the reply Georgi - very much appreciated. However I saw in your sample you just used a stub/test var of new Date(). That got you around the issue and isn't a real example. If you try to use a string value because I have a search text box then it blows up with the error I mentioned. So how do we convert what you created into something that is attached to a real text box as it would be when used in a live application?

  $('#grid').data('kendoGrid').dataSource.filter({
            logic: "or",
            filters: [
                {
                    field: "EnrollmentDate",
                    operator: "gte",
                    value: new Date()
                },
                {
                    field: "EnrollmentDate",
                    operator: "lte",
                    value: new Date()
                }]
        });

[Georgi]

Hi Craig,

The described behavior is expected. When filtering date fields, the value of the filtering criteria must be a date object. It is not possible to compare strings with dates.

A possible solution in your case is to parse the string value before passing it to the filter method:

$("#Grid").data("kendoGrid").dataSource.filter({
    logic: "or",
    filters: [
        {
            field: "DateEntered",
            operator: "gte",
            value: kendo.parseDate(searchValue, "yyyy/MM/dd") //set the format you expect
        },
        {
            field: "DateEntered",
            operator: "lte",
            value: value: kendo.parseDate(searchValue, "yyyy/MM/dd") //set the format you expect
        }]
})

For more detailed information about date parsing, please refer to the following article:

• https://docs.telerik.com/kendo-ui/framework/globalization/dateparsing

Regards,
Georgi
Progress Telerik

[Craig]

Very nice - thank you! That worked great. I have an additional related question if you don't mind. How can I have several fields that are "OR" filters but for the dates I want to do an "AND" so I can do a start and end date range. I don't see a way to do that. Here' s my filtering code. Appreciate any tips/tricks!

 var searchValue = $('#toolBarInput').val();
        $filter = new Array();
        var startDate = $('#dpStartDate').val();
        var endDate = $('#dpEndDate').val();
        if ((startDate != "") && (Date.parse(startDate))) {
            $filter.push({ field: "DateEntered", operator: "gte", value: new Date(startDate) });
        }
        if ((endDate != "") && (Date.parse(endDate))) {
            $filter.push({ field: "DateEntered", operator: "lte", value: new Date(endDate) });
        }
        $filter.push({ field: "InvoiceNumber", operator: "contains", value: searchValue });
        $filter.push({ field: "WarehouseOrderNumber", operator: "contains", value: searchValue });
        $filter.push({ field: "PONumber", operator: "contains", value: searchValue });
        $filter.push({ field: "EndUserPO", operator: "contains", value: searchValue });
        $filter.push({ field: "ShippingAddress.City", operator: "contains", value: searchValue });
        $filter.push({ field: "ShippingAddress.PostalCode", operator: "contains", value: searchValue });
        $filter.push({ field: "ShippingAddress.Name", operator: "contains", value: searchValue });
        var grid = $("#Grid").data("kendoGrid");
        grid.dataSource.filter({
            logic: "or",
            filters: $filter
        });
        grid.dataSource.read();

[Georgi]

Hi Craig,

I have modified the provided code to add AND operator for start and end range fields:

var searchValue = $('#toolBarInput').val();
 $filter = new Array();
 var startDate = $('#dpStartDate').val();
 var endDate = $('#dpEndDate').val();
 var datesFilter = { logic: 'and', filters: [] };
 if ((startDate != "") && (Date.parse(startDate))) {
     datesFilter.filters.push({ field: "DateEntered", operator: "gte", value: new Date(startDate) });
 }

 if ((endDate != "") && (Date.parse(endDate))) {
     datesFilter.filters.push({ field: "DateEntered", operator: "lte", value: new Date(endDate) });
 }

 if (datesFilter.filters.length) {
     $filter.push(datesFilter);
 }

 $filter.push({ field: "InvoiceNumber", operator: "contains", value: searchValue });
 $filter.push({ field: "WarehouseOrderNumber", operator: "contains", value: searchValue });
 $filter.push({ field: "PONumber", operator: "contains", value: searchValue });
 $filter.push({ field: "EndUserPO", operator: "contains", value: searchValue });
 $filter.push({ field: "ShippingAddress.City", operator: "contains", value: searchValue });
 $filter.push({ field: "ShippingAddress.PostalCode", operator: "contains", value: searchValue });
 $filter.push({ field: "ShippingAddress.Name", operator: "contains", value: searchValue });
 var grid = $("#Grid").data("kendoGrid");
 grid.dataSource.filter({
     logic: "or",
     filters: $filter
 });

Regards,
Georgi
Progress Telerik

[Craig]

Thanks Georgi! That is awesome. However one more thing. That works great for the 2 dates to check for a range. However, I need to check for that range and the an AND for all the others. For example: I want all invoices in the range 12/01/2017 to 12/31/2017 that contain KLM. Right now it brings back anything with KLM because it's the date range OR all the other ORs. Hopefully that makes sense.

Thanks!

Craig

[Georgi]

Hello Craig,

Thank you for the explanation. In order to achieve the described behavior change the OR operator to AND within the main filter object:

var searchValue = $('#toolBarInput').val();
 $filter = new Array();
 var startDate = $('#dpStartDate').val();
 var endDate = $('#dpEndDate').val();
 var datesFilter = { logic: 'and', filters: [] };
 if ((startDate != "") && (Date.parse(startDate))) {
     datesFilter.filters.push({ field: "DateEntered", operator: "gte", value: new Date(startDate) });
 }

 if ((endDate != "") && (Date.parse(endDate))) {
     datesFilter.filters.push({ field: "DateEntered", operator: "lte", value: new Date(endDate) });
 }

 if (datesFilter.filters.length) {
     $filter.push(datesFilter);
 }

 $filter.push({ field: "InvoiceNumber", operator: "contains", value: searchValue });
 $filter.push({ field: "WarehouseOrderNumber", operator: "contains", value: searchValue });
 $filter.push({ field: "PONumber", operator: "contains", value: searchValue });
 $filter.push({ field: "EndUserPO", operator: "contains", value: searchValue });
 $filter.push({ field: "ShippingAddress.City", operator: "contains", value: searchValue });
 $filter.push({ field: "ShippingAddress.PostalCode", operator: "contains", value: searchValue });
 $filter.push({ field: "ShippingAddress.Name", operator: "contains", value: searchValue });
 var grid = $("#Grid").data("kendoGrid");
 grid.dataSource.filter({
     logic: "and",
     filters: $filter
 });

Regards,
Georgi
Progress Telerik

[Craig]

Hey Georgi - thanks for the reply. I have tried that howevev that then assumes that all of these fields below are ANDed together and that doesn't work for me. That will never be true. These filters need to be ORs and the dates "ANDs" but the combination of them all need to be ANDed.

 

Kind of like this pseudo-code:

(If date > startDate AND date < endDate)

AND 

(If InvoiceNumber == searchValue OR WarehouseOrderNumber == searchValue OR etc etc etc...)

 

Thanks!

Craig

 

[Georgi]

Hello Craig,

Thank you for the clarification. Below you will find a modified version of the code which demonstrates how to achieve the described logic:

var searchValue = $('#toolBarInput').val();
$filter = new Array();
var startDate = $('#dpStartDate').val();
var endDate = $('#dpEndDate').val();
var datesFilter = { logic: 'and', filters: [] };
var restFilter = { logic: 'or', filters: [] };
if ((startDate != "") && (Date.parse(startDate))) {
    datesFilter.filters.push({ field: "DateEntered", operator: "gte", value: new Date(startDate) });
}

if ((endDate != "") && (Date.parse(endDate))) {
    datesFilter.filters.push({ field: "DateEntered", operator: "lte", value: new Date(endDate) });
}

if (datesFilter.filters.length) {
    $filter.push(datesFilter);
}

 restFilter.filters.push({ field: "InvoiceNumber", operator: "contains", value: searchValue });
 restFilter.filters.push({ field: "WarehouseOrderNumber", operator: "contains", value: searchValue });
 restFilter.filters.push({ field: "PONumber", operator: "contains", value: searchValue });
 restFilter.filters.push({ field: "EndUserPO", operator: "contains", value: searchValue });
 restFilter.filters.push({ field: "ShippingAddress.City", operator: "contains", value: searchValue });
 restFilter.filters.push({ field: "ShippingAddress.PostalCode", operator: "contains", value: searchValue });
 restFilter.filters.push({ field: "ShippingAddress.Name", operator: "contains", value: searchValue });

 $filter.push(restFilter);

var grid = $("#Grid").data("kendoGrid");
grid.dataSource.filter({
    logic: "and",
    filters: $filter
});

Regards,
Georgi
Progress Telerik

[Craig]

Now that is awesome - thank you so much for all your detail and prompt replies. I really appreciate it! Craig

[Dan]

I have need to add a date range filter to a .net core grid in an MVC application and was trying to follow the example attached to this thread - however it is for an older version of .net core and I can't seem to get it running.  Can you provide an updated example for .net core 2.2 and the latest telerik .net core grid?

 

thanks

[Georgi]

Hi Dan,

The sample demonstrates how to add a range filter through the dataSource.filter method.

e.g.

function filter() {
    $('#grid').data('kendoGrid').dataSource.filter({
        logic: "or",
        filters: [
            {
                field: "Birthday",
                operator: "gte",
                value: new Date()
            },
            {
                field: "Birthday",
                operator: "lte",
                value: new Date()
            }]
    });
}

As requested I have assembled a .Net Core 2.2 sample which demonstrates the same.

I hope this helps.


Regards,
Georgi
Progress Telerik