Grid Filters with Date and Time

[Guru]

Have a grid with remote json kendo datasource, and in the schema/data/model I specifiy my column type: "date" and in the grid/columns i specify the format: "{0:MM/dd/yyyy hh:mm:ss}".

If I pull down the column filter and pick equal to 5/29/2012 nothing shows up but if I say is after 5/28/2012 then rows with "5/29/2012 3:15:00" show up... how can I get column filtering to work with time ignored if they only specify date (which is the only option out of box date filtering)?

[Iliana Dyankova]

Hi Zack,

Generally speaking, such functionality is not supported out of the box, but you could achieve it using custom implementation. The approach that I would suggest is to prevent the default filter behavior and create a custom filter function. For convenience I created a simple project which illustrates such approach in action. 


Kind regards,
Iliana Nikolova
the Telerik team

[Vihang]

Hi Iliana,

I am wondering how your solution would work with Remote data binding.
Please guide me to achieve the same from server side using "IEnumerable.ToDataSourceResult(dataSourceRequest)".

Hope this helps,

Thanks & Regards,
Vihang.

[BishMan]

Hello,

Can I have an example how to do this on the server side?  The last comment asked the same thing but no reply.

[Matt]

Hi BishMan,

You can achieve this using two DateTime properties in your binding ViewModel.

e.g.

public class MyViewModel
   {
       public int Id { get; set; }
       public DateTime CreatedDate { get; set; }
       public DateTime CreateDateTime { get { return (this.CreatedDate.Date); } }
   }

Here is a viewModel consists of two properties 'CreatedDate' and 'CreateDateTime'. 'CreateDate' would contain datetime original data from DB. 'CreateDateOnly' would contain date only data calculated from 'CreateDate'.

Now initialise your kendo grid like:

grid = jQuery("#grid").kendoGrid(
            {
                "columns": [
                        { "width": "140px", "title": "Date Created", "field": "CreatedDate", template: '#= kendo.toString(CreatedDateOnly,\"dd MMM yy\") #', "filterable": true, "groupable": true, attributes: { style: "text-align: center;" }}
...
                        ]
           });

Here 'CreatedDate' is bound to the column on which "dataSourceRequest" would generate filterable/groupable clauses. 'CreatedDateOnly' is used just for display purpose to the user.

Hope this helps.

[BishMan]

Hi Matt,

Thank you so much for your response this helped me a ton.   I am running into a different problem.  I want to display the date with date time and I want to filter using the Date only of the date time, but when I sort I want the sorting to work with DateTime as well so that the user can sort records that are run on the same day based on the time of the record.  How Can I tell the grid, to filter using one value and to sort using another value.  I hope my question makes sense

my code is:  

columns.Bound(p => p.TimeCreatedDateOnly)
        .Title("Created")
        .ClientTemplate("#=kendo.toString(TimeCreated,'g')#");

I am able to filter fine, but when I sort I want the time in the date time to be part of the sorting.  Any suggestions?

[Matt]

Hi BishMan,

I am glad that my solution helped you in your challenge.

For your next challenge, you can use this method:

static void ProcessSorts(DataSourceRequest request)
        {
            if (request.Sorts != null)
            {
                foreach (var sort in request.Sorts)
                {
                    if (sort.Member.Equals("TimeCreatedDateOnly", StringComparison.OrdinalIgnoreCase))
                        sort.Member = "TimeCreated";
                }
            }
        }

and then call this method in your action method serverside:

public JsonResult GetData([DataSourceRequest] DataSourceRequest request)
{
     ProcessFilter(request);
     ...
     DataSourceResult result = models.ToDataSourceResult(request);
     return Json(result);
}

Hope this helps.

[BishMan]

Matt, 

Many thanks your advise helped me a ton.  Much appreciated.

[Heiko Falk]

I found a much easier and automatic way to do solve the problem with sorting and filtering of datetime columns:

First of all, we need a class that inherits from DataSourceRequestAttribute (which is applied to all reqest parameter of grid read actions):

namespace MyNameSpace.Attributes
{
    using System.Web.Mvc;
    using Kendo.Mvc.UI;
    using KVpro.WebPflege.Definitions.ModelBinder;


    /// <summary>
    /// This class serves as a wrapper for the Kendo DataSourceRequestAttribute, which maps
    /// the incoming request data in aDataSourceRequest Object. This usually happens with the
    /// DataSourceRequestModelBinder. To get the result of the kendo model binder
    /// we need to use a custom model binder CustomDataSourceRequestModelBinder
    /// </summary>
    public class CustomDataSourceRequestAttribute : DataSourceRequestAttribute
    {
        public CustomDataSourceRequestAttribute(string gridName)
        {
            this.GridName = gridName;
        }

        public string GridName { get; set; }

        public override IModelBinder GetBinder()
        {
            return new CustomDataSourceRequestModelBinder() { GridName = this.GridName };
        }
    }
}

The CustomDataSourceRequestModelBinder:

namespace MyNamespace.ModelBinder
{
    using System;
    using System.Web.Mvc;
    using Kendo.Mvc;
    using Kendo.Mvc.UI;


    /// <summary>
    /// This class is used by the CustomDataSourceRequestAttribute, which again is beeing
    /// adapted in all read methods for kendo grids. The current binding context is beeing
    /// passed to the original kendo modelbinder. We then transform critical datetime filter
    /// in a correct format and return the request to the action
    /// </summary>
    public class CustomDataSourceRequestModelBinder : IModelBinder
    {
        public string GridName { get; set; }


        public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
        {
            // Get an instance of the original kendo model binder and call the binding method
            DataSourceRequestModelBinder baseBinder = new DataSourceRequestModelBinder();
            DataSourceRequest request = (DataSourceRequest)baseBinder.BindModel(controllerContext, bindingContext);

            // If there are any filters, we may transform those which are date filters
            if (request.Filters.Count > 0)
            {
                foreach (IFilterDescriptor filterDescriptor in request.Filters)
                {
                    this.SetDateTimeFilter(filterDescriptor);
                }
            }

            return request;
        }

        /// <summary>
        /// If a date is filtered, we need to set the time of the date to either 00:00:00 or 23:23:59.
        /// It's actually 00:00:00 by default, we do both ways just for convenience.
        /// </summary>
        private void SetDateTimeFilter(IFilterDescriptor typeFilterDescriptor)
        {
            if (typeFilterDescriptor is CompositeFilterDescriptor)
            {
                CompositeFilterDescriptor compositeFilterDescriptor = typeFilterDescriptor as CompositeFilterDescriptor;

                foreach (IFilterDescriptor filterDescriptor in compositeFilterDescriptor.FilterDescriptors)
                {
                    this.SetDateTimeFilter(filterDescriptor);
                }
            }
            else
            {
                FilterDescriptor filterDescriptor = typeFilterDescriptor as FilterDescriptor;

                if (filterDescriptor.Value is DateTime)
                {
                    DateTime value = (DateTime)filterDescriptor.Value;

                    if (filterDescriptor.Operator == FilterOperator.IsGreaterThan
                        || filterDescriptor.Operator == FilterOperator.IsGreaterThanOrEqualTo)
                    {
                        filterDescriptor.Value = new DateTime(value.Year, value.Month, value.Day, 0, 0, 0);
                    }
                    else if (filterDescriptor.Operator == FilterOperator.IsLessThan
                             || filterDescriptor.Operator == FilterOperator.IsLessThanOrEqualTo)
                    {
                        filterDescriptor.Value = new DateTime(value.Year, value.Month, value.Day, 23, 59, 59);
                    }
                    else
                    {
                        throw new Exception(string.Format("Grid {0}: Not accepted filter für {1}. Operator {} is not supported", this.GridName, filterDescriptor.Member, filterDescriptor.Operator));
                    }
                }
            }
        }
    }
}

So the only thing we need to do in future, is to use the CustomDataSourceRequestAttribute instead of  the Kendo DataSourceRequestAttribute:

[HttpPost]
        public ActionResult ReadProducts([CustomDataSourceRequestAttribute(ConstantsProvider.GridNames.Aenderungsverfolgung)] DataSourceRequest request)
        {
            DataSourceResult result = this.GetProducts().ToDataSourceResult(request);

            return Json(result);
        }

Simple as that!

Regards
Heiko

[Grady]

Heiko, I just wanted to thank you for your post.  You saved me hours of time probably.  Thank you!

[Heiko Falk]

Grady, you're welcome, glad that someone adapted my workaround!

[Mario]

$("th[data-title=Date]")
                .data("kendoFilterMenu")
                    .form

Your attached source works pretty well with old version of Kendo Grid (2012), in newer version ".form" is not available as shown in this example. Can you please tell me the way to get to the form in latest version of Kendo Grid ? 

[Nicolo]

Hi Iliana

How can this custom filter be implemented with AngularJS? Could you provide an adapted version of your sample project to illustrate this?

Kind regards
Nicolo

[Iliana Dyankova]

Hi Nicolo,

This thread is relatively old and outdated. In the latest versions Kendo UI Grid provides different filtering templates - check the following options: 
- Columns.filterable.ui
- Columns.filterable.cell
- Columns.filterable.multi
If you have any particular questions please open new conversations for them.

Regards,
Iliana Nikolova
Telerik

 

 

[Iliana Dyankova]

Hi Mario,

In order to get the form I would suggest to use the suggested logic in the filterMenuInit (instead of the $(document).ready()).

Regards,
Iliana Nikolova
Telerik