Ignoring time when filtering dates in Telerik Kendo grids

kendofilteringTo the right is what the filter looks like on a Telerik Kendo grid when Filterable(true) is set on a DateTime column.

If I was a user, I would expect the grid to return all rows that match the date (8/13/14) regardless of the time associated with that date. If it’s 8/13/14 02:00 or 8/13/14 17:41, the expectation is that they should all appear because I am asking the grid to show me all the data that occurred on that date.

Instead, the Kendo grid defies that expectation and will only return data that precisely matches the date, at midnight, ie. 8/13/14 00:00:00. I’ve had users who were convinced this functionality is actually a defect, when it was just a case of it being really unintuitive.

So, the goal is to modify the filtering behavior in the grid to effectively ignore the time, and only use the literal date when filtering data. But, still preserve the ability to sort the data by time.

After doing the prerequisite search around the Telerik forums and StackOverflow, it became quite clear that existing solutions hacks are really messy and either involve some trickery in the underlying model that is bound to the grid (ewww, no) or some nasty JavaScript (for the love of kittens, no).

The basis of my solution involves making use of a custom DataSourceRequest attribute that implements a custom model binder. The custom model binder will iterate through the filters being applied to the grid and transform them accordingly.

What do I mean by transform? Here are some examples of what happens:

isEqual("08/13/14")

becomes:

IsGreaterThanOrEqual("08/13/14 00:00:00") AND IsLessThanOrEqual("08/13/14 23:59:59")

And another example:

isLessThanOrEqual("08/13/10") AND isEqual("08/13/14")

becomes:

isLessThanOrEqual("08/13/10 23:59:59") AND IsGreaterThanOrEqual("08/13/14 00:00:00") AND IsLessThanOrEqual("08/13/14 23:59:59")

Using the same logic, I apply it to all the other possible logical operators when filtering (is not equal to, is greater than, is equal to, etc.)

So first, lets starting extending the default Kendo DataSourceRequest attribute:

We will use this attribute to decorate our request data when reading data for our grid. Next, is the heart of our solution which is the custom model binder:

First, notice the recursive calls to TransformFilterDescriptors(), this is to handle cases where the user may be requesting two or more different filters for a field. If you read through the comments in the code you will see where the original filter logic is being translated into a single or composite filter with the time set to 00:00:00 or 23:59:59 to match the appropriate situation.

Finally, we decorate the Kendo DataSourceRequest being passed into our Actions with our new [CustomDataSourceRequest] attribute. Here is what a basic Action would look like:

The added benefit of this is there is absolutely no front end work – no javascript or view model tweaking, and no page or model specific modifications. The solution is generic enough to work across all the grids and models in your application.

The full code from this post is available on Github as a Gist.

Update (2015/05/03): While at the Build 2015 conference I had a chance to speak with some of the folks at Telerik working on Kendo UI. While they do acknowledge that the current DateTime filter behavior isn’t very intuitive, their concern with making it the default is that it will affect people who expect that functionality in existing applications. So it looks like we have to make do with the solution above, at least for now.

Update (2017/11/28): Updated the code to handle the “Is Null” and “Is Not Null” filters for nullable dates. Also updated the logic to support high precision DateTime values. I also want to make a note that if you are filtering UTC DateTime objects, you will need to add a call to  .ToUniversalTime() at the end of any DateTime constructors inside the main switch loop of the TransformFilterDescriptors() method.

Share on FacebookShare on Google+Share on RedditTweet about this on TwitterShare on StumbleUponEmail this to someone

15 Comments

  1. Prasad · January 28, 2015 Reply

    Thanks a ton for your code. It helped me a lot.

    There is small bug with NotEqual logic. In not equal logic operator should be ‘OR’ not ‘AND’.

  2. mic · February 3, 2015 Reply

    Hi,

    Great solutin but I have a question:
    Under what license is that code?

    Thanks

  3. Rahul Khera · February 24, 2015 Reply

    Perfect, just what I was looking for!

  4. rstraszewski · May 13, 2015 Reply

    Hi, great solution, is something similiar possible for grouping? So I can group by day (date) and not by date and time…

  5. Dizzy · May 26, 2015 Reply

    Do you have a Web-API compatible solution?

  6. Jozimar Back · July 30, 2015 Reply

    Great solution, helped me a lot.
    Thanks bro.

  7. kamel · October 19, 2015 Reply

    Thank you very much is very helpful 🙂

  8. Augusto Barreto · December 9, 2015 Reply

    Is there an equivalent solution when using Web API OData v4?

  9. Ralf · December 11, 2015 Reply

    Nice solution. Made a little adjustment (because in our system milliseconds are relevant):
    Instead of using 23:59:59 of the day from the filter value as upper bound, we use 00:00:00 of next day (construction of filters should also be slightly adjusted).
    Otherwise records that have values in last second of the day (for example 23:59:59:559) won’t be treated correctly.

  10. Trailmax · February 24, 2016 Reply

    Yeah, great solution. Integrated nicely. Thanks a lot for sharing, love it!

  11. Bob Bryenton · July 20, 2016 Reply

    Regarding your update. I don’t know why Telerik wouldnt include your solution as just a config option on the date. Set the column type to date only, use your solution. Set the date, even leave it as default, and you get the date/time filter. Sometimes I just scratch my head (why I think Im going bald)

  12. Ajesh · October 6, 2016 Reply

    Hi,
    I was trying to implement this solution. But not working for some reason.
    BindModel method in CustomDataSourceRequestModelBinder gets executed only once when the data is getting loaded in the grid initially. At that time request.Filters.Count is zero as there are no filters applied initially. After filter, this method never gets executed again, and hence TransformFilterDescriptors method is not getting called.

    Did i do anything wrong because of which this is not working? could you please help

    Thanks
    Ajesh

  13. Dragos · September 12, 2017 Reply

    Hello, great code.

    You can also use it as an extension:

    namespace Kendo.Mvc.Extensions
    {
    public static class CustomKendoMvcExtensions
    {
    ///
    /// Changes DataSourceRequest Filters in case of Datetime comparison. Does not take time into consideration
    /// //http://www.crowbarsolutions.com/ignoring-time-when-filtering-dates-in-telerik-kendo-grids/
    /// Adapted from above link.
    /// Uses CustomDataSourceRequestModelBinder.TransformFilterDescriptors function
    ///
    ///
    ///
    ///
    public static DataSourceResult ToCustomDataSourceResult(this IQueryable queryable, DataSourceRequest request)
    {
    if (request.Filters != null && request.Filters.Count > 0)
    {

    ModelBinders.CustomDataSourceRequestModelBinder customModelBinder = new ModelBinders.CustomDataSourceRequestModelBinder();

    var transformedFilters = request.Filters.Select(customModelBinder.TransformFilterDescriptors).ToList();
    request.Filters = transformedFilters;
    }

    return queryable.ToDataSourceResult(request);
    }
    }
    }

Leave a Reply