Ignoring time when filtering dates in Telerik Kendo grids
To 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.
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’.
Good catch, I have updated the code with the fix.
Hi,
Great solutin but I have a question:
Under what license is that code?
Thanks
I’ve included a basic MIT license in the gist, so basically do whatever you want with the code because I’m not liable 🙂
Perfect, just what I was looking for!
Hi, great solution, is something similiar possible for grouping? So I can group by day (date) and not by date and time…
Do you have a Web-API compatible solution?
Great solution, helped me a lot.
Thanks bro.
Thank you very much is very helpful 🙂
Is there an equivalent solution when using Web API OData v4?
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.
Yeah, great solution. Integrated nicely. Thanks a lot for sharing, love it!
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)
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
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);
}
}
}
Hello,
I tried to do the same for one grid in my application and it worked perfect. But the same CustomDataSourceRequest used for another grid in my application and it didn’t work. Do you what could be the reason?
Okay. I understand. This is due to the ServerOperation(false) in grid. Is there any way to achieve this in client side alone?
Thank you very much, saved me several headaches. You should post your solution also on Telerik forum for reference.
I’m using Kendo UI 2016 and it seems that your solution still is the only proper way of solving these issues.
Looks like the link to code has expired , i cant see the entire code. Please help.
thanks! work very well
Is it available with Angular Kendo grid as well??
Thank you so much it worked for me.
Thank you so much!!! It worked for me…
SuperB solution brother. It worked.
Thank you for sharing 🙂
Hi, I see one of our app also using this which is now being upgraded to .net 6. Can you please help with how will it get handled there?
How to do this for .net core?
Thanks for the solution.