Grid

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.