This is a migrated thread and some comments may be shown as answers.

Filtering on DateTime data type

12 Answers 301 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Craig
Top achievements
Rank 1
Craig asked on 22 Nov 2017, 11:03 PM

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

12 Answers, 1 is accepted

Sort by
0
Georgi
Telerik team
answered on 27 Nov 2017, 10:54 AM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Craig
Top achievements
Rank 1
answered on 27 Nov 2017, 06:13 PM
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()
                }]
        });
0
Georgi
Telerik team
answered on 30 Nov 2017, 12:26 PM
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:



Regards,
Georgi
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Craig
Top achievements
Rank 1
answered on 30 Nov 2017, 07:28 PM

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();

0
Georgi
Telerik team
answered on 05 Dec 2017, 01:44 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Craig
Top achievements
Rank 1
answered on 05 Dec 2017, 04:00 PM

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

0
Georgi
Telerik team
answered on 06 Dec 2017, 02:12 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Craig
Top achievements
Rank 1
answered on 11 Dec 2017, 08:22 PM

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

 

0
Georgi
Telerik team
answered on 12 Dec 2017, 08:02 AM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Craig
Top achievements
Rank 1
answered on 13 Dec 2017, 08:53 PM
Now that is awesome - thank you so much for all your detail and prompt replies. I really appreciate it! Craig
0
Dan
Top achievements
Rank 1
Veteran
answered on 30 Aug 2019, 05:58 PM

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

0
Georgi
Telerik team
answered on 04 Sep 2019, 08:27 AM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Tags
Grid
Asked by
Craig
Top achievements
Rank 1
Answers by
Georgi
Telerik team
Craig
Top achievements
Rank 1
Dan
Top achievements
Rank 1
Veteran
Share this question
or