Datetime filter in kendo grid

Question

My code is in C# .NET I am using Kendo Grid version 2013.2.716.340 and server binding to show data in grid.

In Kendo UI Grid, I have a dateTime column but the column filter input only has a date picker but no time picker. Due to this if I select the option IsEqualTo and give a date then I get zero results as the time is set to 00:00:00 in the filter but the columns have some time value. I want to add time picker along with date picker.

I tried to do this on my column, but it didn't work:

columns.Bound(o => o.Time).Title("Time").Format("{0:MM/dd/yyyy HH:mm:ss}").Filterable(f => f.UI("DateTimeFilter")).Width("5%");

And have applied below script :

<script type="text/javascript">
  function DateTimeFilter(control) 
  {
    $(control).kendoDateTimePicker();
  }
</script>

The above code works when I select exact datetime from datetimepicker but it doesn't work when I select isequalto. For eg : If I have this datetime "12/21/2013 07:15:45" displayed in my kendo grid column and when I copy this datetime to isequalto option under filter it does not gives any data.

Also I tried the example provided on this link It also didn't work in my case. Example on this link uses Ajax binding. I need to apply it in case of server binding.

This is an attached image that shows what I want to apply. Here is the link for image. If I copy the datetime shown in grid to the filter It should filter correctly and give result.

I will be very thankful if anybody could help me out in solving my issue. Thanks in advance.

Solution 1

From my experience, the kendoDateTimePicker is really picky; if the format of the filter cannot specify the datetime precision of the column data, it will not find it.

In your case, your column format is "MM/dd/yyyy HH:mm:ss" (with seconds). The default format for the kendoDateTimePicker is "MM/dd/yyyy h:mm tt" (without seconds and hour spec is mismatched). Since you initialized a default kendoDateTimePicker, no matter what you put in the picker, you could never filter to a date that IS EQUAL TO a column value since you couldn't input how many seconds it was.

The easiest way to ensure it works is to use the same format for both column and the kendoDateTimePicker . Replace your DateTimeFilter function with this:

function DateTimeFilter(control) 
{
   $(control).kendoDateTimePicker({
      format: "MM/dd/yyyy HH:mm:ss",
      timeFormat: "HH:mm:ss"
   });
}

With regards to the kendoDateTimePicker:

• format defines the input value format for control
• timeFormat defines the time format of the time picker
• interval (didn't use it above), but it specifies the time interval in minutes between each option of the time picker.

I am not using asp.net mvc, so I'm not 100% sure if this solves your problem. However I am certain it will clear up at least some of the filtering issues you have. I can provide a jsfiddle for a purely html/javascript sample if you want.

Solution 2

I know I am late with this answer, but it might still help someone.

The above code works when I select exact datetime from datetimepicker but it doesn't work when I select isequalto. For eg : If I have this datetime "12/21/2013 07:15:45" displayed in my kendo grid column and when I copy this datetime to isequalto option under filter it does not gives any data.

I guess you are experiencing this because your server-side DateTime values contain fractional second data as well and the equals operator does not ignore them at comparison. I have found it easier to come up with a server-side solution instead of writing all sort of dirty JS workarounds.

The idea is that whenever you find a filter in the DataSourceRequest object that would filter on a DateTime property, you manually replace it with a CompositeFilterDescriptor, which truncates the value to the desired precision, sets it as the lower bound and then adds one unit of the desired precision (sec, min, hour, etc.) and sets it as the upper bound.

The code is the following:

public static class KendoHelpers
{
    public enum DateTimePrecision
    {
        Seconds = 1,
        Minutes = 2,
        Hours = 4
    }

    public static DataSourceRequest NormalizeDateFilters(this DataSourceRequest request, DateTimePrecision precision)
    {
        // TODO: Add parameter validation.

        for (int i = 0; i < request.Filters.Count; ++i)
        {
            FilterDescriptor filter = request.Filters[i] as FilterDescriptor;
            if (filter != null && filter.ConvertedValue is DateTime && filter.Operator == FilterOperator.IsEqualTo)
            {
                DateTime val = (DateTime)filter.ConvertedValue;

                CompositeFilterDescriptor newFilter = new CompositeFilterDescriptor
                {
                    LogicalOperator = FilterCompositionLogicalOperator.And
                };

                DateTime lowerBound;
                DateTime upperBound;

                if (precision == DateTimePrecision.Seconds)
                {
                    lowerBound = val.TruncateToWholeSeconds();
                    upperBound = lowerBound.AddSeconds(1);
                }
                else if (precision == DateTimePrecision.Minutes)
                {
                    lowerBound = val.TruncateToWholeMinutes();
                    upperBound = lowerBound.AddMinutes(1);
                }
                else if (precision == DateTimePrecision.Hours)
                {
                    lowerBound = val.TruncateToWholeHours();
                    upperBound = lowerBound.AddHours(1);
                }
                else
                {
                    // If someone would be stupid enough to supply Hours | Minutes
                    throw new ArgumentException("Not supported precision. Only Second, Minute, Hour values are supported.", "precision");
                }

                newFilter.FilterDescriptors.Add(new FilterDescriptor
                {
                    Member = filter.Member,
                    MemberType = filter.MemberType,
                    Operator = FilterOperator.IsGreaterThanOrEqualTo,
                    Value = lowerBound
                });

                newFilter.FilterDescriptors.Add(new FilterDescriptor
                {
                    Member = filter.Member,
                    MemberType = filter.MemberType,
                    Operator = FilterOperator.IsLessThan,
                    Value = upperBound
                });

                request.Filters[i] = newFilter;
            }
        }

        return request;
    }
}

Remarks:

• The DateTime truncater extension is based on this answer.
• This method will only do anything if the operator is equals, because if you select Is later than or the like, the default behavior will work just as well.
• This method does not care about any present CompositeFilterDescriptors becasue an expression dateToSearch = 2016-11-21 11:22:00 AND dateToSearch = 2016-11-21 11:59:00 makes no sense anyway.
• Similar thing could be done for DateTimeOffset values.

Solution 3

An enhancement to Balázs' answer, this assumes that you are using a simple date portion of DateTime and don't care about the time portion at all. It is also recursive to handle being filtered with other unrelated filters.

public static IList<IFilterDescriptor> NormalizeDateFilters(this IList<IFilterDescriptor> filters)
{
    for (var i = 0; i < filters.Count; i++)
    {
        if (filters[i] is CompositeFilterDescriptor compositeFilterDescriptor)
        {
            compositeFilterDescriptor.FilterDescriptors.NormalizeDateFilters();
        }
        else if (filters[i] is FilterDescriptor filterDescriptor &&
                 filterDescriptor.ConvertedValue is DateTime &&
                 filterDescriptor.Operator == FilterOperator.IsEqualTo)
        {
            var value = DateTime.Parse(filterDescriptor.Value.ToString());
            var start = value.Date;
            var end = start.AddDays(1);

            var newFilter = new CompositeFilterDescriptor
            {
                LogicalOperator = FilterCompositionLogicalOperator.And
            };

            newFilter.FilterDescriptors.Add(new FilterDescriptor
            {
                Member = filterDescriptor.Member,
                MemberType = filterDescriptor.MemberType,
                Operator = FilterOperator.IsGreaterThanOrEqualTo,
                Value = start
            });

            newFilter.FilterDescriptors.Add(new FilterDescriptor
            {
                Member = filterDescriptor.Member,
                MemberType = filterDescriptor.MemberType,
                Operator = FilterOperator.IsLessThan,
                Value = end
            });
            filters[i] = newFilter;
        }
    }
    return filters;
}

Comments

[kumawatp]

Thanks a lot. It solved my problem in case of date and datetime column but I still have have issue in case if I have time column in kendo grid.

[zolty13]

Hi, How to make Unit test for this class? I have no idea how to mock it and use in tests. Your code helps me a lot ;)