Filter not working for DateTime columns with "equal To" filter

13 posts, 0 answers
  1. Matt
    Matt avatar
    23 posts
    Member since:
    Mar 2011

    Posted 22 Nov 2011 Link to this post

    I can't get the filters for 'equal to' working on date time column in rad grid. As far as I can tell this has something to do with the times on the dates. I have seen many posts about this here with no answer so I guess I don't really expect help, but just throwing my voice in with everyone else saying your filters don't work right for date time columns. Period.
  2. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 22 Nov 2011 Link to this post

    Hello,

    I have tried the same and worked as expected. You can take a look into the following demo.
    Grid / Basic Filtering

    Thanks,
    Princy.
  3. Preeti
    Preeti avatar
    7 posts
    Member since:
    Jul 2013

    Posted 26 Jul 2013 Link to this post

    I tried the code shown in the demo but the filter does not work for me.
    The date time column in database is saved as '2013-07-26 00:01:09.840' format. whereas in the grid the value is displayed as '7/26/2013 12:01:09 AM'. This should not be a problem as far as I think.
    I am giving the value '7/26/2013 12:01:09 AM' in the text box to search with Filter option as 'Equal To'. No results are returned when I have selected this value from the grid itself.

    Greater than and Less Than filter options work fine. Just the equal to is not.

    Please tell me what is wrong here.
    Thanks
  4. Eyup
    Admin
    Eyup avatar
    3727 posts

    Posted 31 Jul 2013 Link to this post

    Hi Preeti,

    This behavior is expected since the "2013-07-26 00:01:09.840" is not equal to "2013-07-26 00:01" which is the value used for filtering. The picker assumes the entered date to have starting seconds and milliseconds: "2013-07-26 00:01:00.000", which makes it different than the desired: "2013-07-26 00:01:09.840".

    To resolve this issue, you can either enable the following property:
    <telerik:GridDateTimeColumn ... EnableTimeIndependentFiltering="true">
    Or prevent the filter command when the selected function is EqualTo and use Contains function instead.

    Hope the clarification was helpful.

    Regards,
    Eyup
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  5. Preeti
    Preeti avatar
    7 posts
    Member since:
    Jul 2013

    Posted 06 Aug 2013 Link to this post

    Hi Eyup,
    Thanks a lot for the reply.
    I need to understand what value comparison is going on by the telerik filter/grid.

    Because the grid column shows the value  '7/26/2013 12:01:09 AM' and this is what I am entering in the filter textbox. so ideally it should fetch the data correctly.

    Also I don't have the option of EnableTimeIndependentFiltering.I think the version of control I am using is different. am I right?

    Please let me know a way to make this work.'

    Thanks,
    Preeti
  6. Kunal Chowdhury
    Kunal Chowdhury avatar
    23 posts
    Member since:
    May 2010

    Posted 06 Aug 2013 Link to this post

    Hi Preeti,

    Eyup is right. The value that you have in database is '2013-07-26 00:01:09.840' and when you bind it to DataGrid and display in the UI, it shows like this: '2013-07-26 00:01:09' where 840 milliseconds different is there. Now, when you are doing a comparison with EqualTo() operator, you are just neglecting that delta value 840. Thus the comparison returns false.

    In case you want to implement that functionality, you have to override the EqualTo() operator or write some additional logic to compare both the sides without that delta difference.

    Hope this clarifies your query. Let us know, if you still have any queries on this.

    Regards,
    Kunal Chowdhury
    www.kunal-chowdhury.com

    (Mark as answer, if this helped you)
  7. Preeti
    Preeti avatar
    7 posts
    Member since:
    Jul 2013

    Posted 06 Aug 2013 Link to this post

    Thank you Kunal. Let me try out what you and Eyup have suggested and I will come back if I have any questions.

    Thanks Again,
    Preeti
  8. Vishal
    Vishal avatar
    2 posts
    Member since:
    Sep 2009

    Posted 16 Sep 2016 in reply to Kunal Chowdhury Link to this post

    I have formatted the Editor text box the date time picker etc to have the milliseconds but still it is not filtering, as the filter descriptor does not have milliseconds 
  9. Eyup
    Admin
    Eyup avatar
    3727 posts

    Posted 21 Sep 2016 Link to this post

    Hello Vishal,

    Have you tried setting the following property?
    <telerik:GridDateTimeColumn ... EnableTimeIndependentFiltering="true">

    Regards,
    Eyup
    Telerik by Progress
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  10. Tyler
    Tyler avatar
    2 posts
    Member since:
    Aug 2018

    Posted 18 Oct 2018 Link to this post

    I am having trouble filtering on a date/time column with 'eq' as well.  After some research I was able to find a reference to filterMenuOpen (I've also attempted using filterMenuInit) and used a function to search by two dates/times and the values between.  It works to filter by equals to even to the second, but I am getting odd behavior from the other columns in my grid.  In my grid I have a column for filename(sting), upload date/time(date), import status(string), and import date/time(date).  If I first filter by the upload date/time, I am able to filter additionally on the other two string columns, but not on the second date/time column.  If I first filter by any string column, the date columns will not filter.  I've used a custom filterable.ui function and formatting of the date columns in order to allow the user to copy and paste a date/time from the dates column.

    I really need to be able to search for a date/time down to the second as the users are using this for auditing.  Additionally, the users may need to apply additional filters on the other columns.  How do I make this happen?  Here is my code so far:

    function upImpDateTimeFilter(control) {  //this is the function used by the filterable ui
        var expdatepic = $(control).kendoDateTimePicker({
        format: "MM/dd/yyyy HH:mm:ss",
        timeFormat: "HH:mm:ss"});
    }

    filterMenuOpen: function gridFilter(e) {
        var firstDropDown = $('[data-bind="value: filters[0].operator"]').data('kendoDropDownList');
        $('button[type="submit"]').click(function (ev) {
           ev.preventDefault;
           if (firstDropDown.value() === 'eq') {
               ev.preventDefault();
               var selectedDateTime = $('[data-bind="value:filters[0].value"]').val();
               var selectedDateTimeTwo = $('[data-bind="value: filters[1].value"]').val();
               var selectedDateObj = new Date(selectedDateTime);
               if (!selectedDateTime) {
                    $(ev.target).closest('[data-role="popup"]').data('kendoPopup').close();
                    return;
               }
               var startOfFilterDate = new Date(selectedDateObj.getFullYear(), selectedDateObj.getMonth(), selectedDateObj.getDate(),selectedDateObj.getHours(), selectedDateObj.getMinutes(), selectedDateObj.getSeconds());
               var endOfFilterDate = new Date(selectedDateObj.getFullYear(), selectedDateObj.getMonth(), selectedDateObj.getDate(), selectedDateObj.getHours(), selectedDateObj.getMinutes(), selectedDateObj.getSeconds() + 1);
               var filter = {
                    logic: "and",
                    filters: [
                          { field: e.field, operator: "gte", value: startOfFilterDate },
                          { field: e.field, operator: "lte", value: endOfFilterDate }
                               ]
              };
    e.sender.dataSource.filter(filter);
    $(ev.target).closest('[data-role="popup"]').data('kendoPopup').close();
    return;
    }
    });
    }

    Why is it that this custom filter affects the other columns in grid when the firstDropDown.value criteria is not met?  Am I missing something with the e.send.dataSource.filter(filter)?

  11. Eyup
    Admin
    Eyup avatar
    3727 posts

    Posted 23 Oct 2018 Link to this post

    Hi Tyler,

    Generally, filtering date field includes the time part, too:
    https://demos.telerik.com/kendo-ui/grid/filter-menu-customization

    Filtering only on date requires explicit coding:
    https://docs.telerik.com/kendo-ui/controls/data-management/grid/how-to/filtering/filter-by-date

    I suggest that you check the live sample provided in the first link above. If you have further questions, you can post your question in the Kendo forums or open a formal support thread and select the Kendo in the Product category.

    Regards,
    Eyup
    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.
  12. Tyler
    Tyler avatar
    2 posts
    Member since:
    Aug 2018

    Posted 24 Oct 2018 in reply to Eyup Link to this post

    Thank you Eyup,

    The second link was most helpful.  I was partially to the solution and still am.  Using the parse on the data source to create an independent field had the desire result.  I am facing a second issue now.  This solution works fine when there is a single date/time column in the grid, but I have 2 date fields and 2 string fields.  If I filter by the 'eq' on either date field, I can also filter on the string fields.  But, when I try to filter on both date fields, I get no results.  I added a simple alert to identify e.field before the filter is set.  I get the e.field for the first date column, but when I try to filter by 'eq' on the second date column, I get no alert and no results.

     

    Does Progress have a solution to filter by 'eq' on more than one date/time fields?

  13. Eyup
    Admin
    Eyup avatar
    3727 posts

    Posted 29 Oct 2018 Link to this post

    Hi Tyler,

    You can find additional details about the filter method of the datasource widget here:
    https://docs.telerik.com/kendo-ui/api/javascript/data/datasource/configuration/filter

    You will notice that it also has an option to join multiple filters with OR statement in addition to AND. A practical implementation you can find here:
    https://docs.telerik.com/kendo-ui/knowledge-base/filter-all-columns-with-one-textbox

    I hope this will prove helpful. For additional questions, you can open a new thread and select the appropriate product as suggested in my previous reply.

    Regards,
    Eyup
    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.
Back to Top