Filtering/Sorting a DateTime column in Kendo Grid

Question

I have a datetime column in my Kendo Grid. The values look like this: 2014-04-11 12:43:41.720

In my grid, I have changed the template to just display them as short dates: 04/11/2014

The reason I don't just send data to the columns preformatted is because I want the time to be taken into account for sorting. My problem now is that when filtering, specifically when doing an "equal to" filter, if I select 4/11/2014 from the datepicker, it will not show any results because the time is by default 12:00:00.00.

Is there anyway I can filter based on the text instead of the value? Or can I send preformatted dates to the grid and have the sort use a different field?

JS snippet of my column:

columns: [
    { 
      field: "CREATEDATE",
      title: "Created",
      width: 78,
      template: '#= kendo.toString(kendo.parseDate(CREATEDATE, "yyyy-MM-dd"), "MM/dd/yyyy") #',
      filterable: true,
      attributes: { style: "text-align:center;" }
    }
]

Answer 1

I realize this is an old post and seemingly resolved, but I stumbled across this when I ran into this question and found an easier solution for the second part of your question: "can I have the sort use a different field." The KendoUI data grid has a configuration which supports this.

For example, if I have a column in the grid which shows who last changed a row and the date they changed it (e.g. "John Doe - 7/20/16 10:30 AM", see 'changedByDetail' in the code example) and when the user sorts on the column I only want the datetime aspect sorted, I can achieve this as such:

    gridColumns: [
                { field: 'title', title: 'Title' },
                { field: 'changedByDetail', title: 'Changed'
                    , sortable: {
                        compare: function (a, b, desc){
                            if (a.changedDateTime < b.changedDateTime) {
                                return -1;
                            }
                            else if (a.changedDateTime > b.changedDateTime) {
                                return 1;
                            }
                            return 0;
                        }
                    }
                }
            ]

In this case, my grid datasource contains the datetime in the 'changedDateTime' field in my example. If it didn't, you could likely use a string parse function to strip the datetime from the string, convert to date and do the compare off that.

Hope this helps someone!

Answer 2

There is another way to show the time with which will is filterable and sortable. I use this in my KendoUI jquery grid and can filter minutes.

{ field: "CREATEDATE", 
  title: "Created",
  format: "{0:dd/MM/yy hh:mm tt}",
  width: "150px",
  template: "#= kendo.toString(kendo.parseDate(CREATEDATE, 'yyyy-MM-dd hh:mm tt'), 'dd/MM/yyyy hh:mm tt') #" },

Answer 3

A solution for my initial issue of filtering was resolved by jwatts, but I wanted to include another answer for others as I ended up changing my code to address my second question:

"Or can I send preformatted dates to the grid and have the sort use a different field?"

I did end up using the preformatted date in my grid as the column value. Filtering worked well because I didn't have to worry about timestamps. To fix the sorting issue, in my controller (on rebind) I checked DataSourceRequest for any sort parameters and if the user was sorting the preformatted date column, I switched it instead to use the full datetime column (hidden).

if (request.Sorts != null)
{
    foreach (var sort in request.Sorts)
    {
        if (sort.Member.Equals("CREATEDATE_FORMATTED", System.StringComparison.OrdinalIgnoreCase))
        {
            sort.Member = "CREATEDATE";
        }
    }
}

Answer 4

The Telerik forum has an example of this here for download.

For convenience I will paste the code that they created. The lower third of the example contains the javascript/jquery needed to hijack the built-in Kendo filter operation and allow you to provide your own handling.

Basically, you have to build a new date object using on the portions of the date that you are interested in, ie. day, month, and year.

<html>
<head>
    <title>Dynamically change date format</title>
    <script src="http://code.jquery.com/jquery-1.7.1.min.js"></script>
    <script src="http://cdn.kendostatic.com/2012.1.515/js/kendo.all.min.js"></script>
    <link href="http://cdn.kendostatic.com/2012.1.515/styles/kendo.common.min.css" rel="stylesheet" />
    <link href="http://cdn.kendostatic.com/2012.1.515/styles/kendo.default.min.css" rel="stylesheet" />
</head>
<body>

    <input id="dropDownList"></input>
    <br />
    <div id="grid"></div>

    <script>
        var sampleData = [
            { name: "John", date: new Date("October 13, 2012 11:13:00")},
            { name: "Lisa", date: new Date("March 18, 2012 07:13:00")},
            { name: "Jack", date: new Date("September 28, 2011 12:28:43")},
            { name: "Maria", date: new Date("May 28, 2012 11:13:00")},
            { name: "Steven", date: new Date("May 31, 2012 07:13:00")},
            { name: "Bob", date: new Date("September 02, 2012 12:28:43")}
        ];

        var flag = 1;

        function formatDate(date) {
            switch(flag)
            {
                case 1:
                    return kendo.toString(date, "g");
                case 2:
                    return kendo.toString(date, "d");
                case 3:
                    return kendo.toString(date, "Y");
                default:
                    return kendo.toString(date, "F");
            }
        }

        $("#dropDownList").kendoDropDownList({
            dataTextField: "text",
            dataValueField: "value",
            dataSource: [
                { text: "Weekly", value: "1" },
                { text: "Monthly", value: "2" },
                { text: "Yearly", value: "3" }
            ],
            index: 0,
            change: function(e) {
                flag = parseInt(this.value());
                console.log(flag);
                $("#grid").data("kendoGrid").refresh();
            }
        });

        $("#grid").kendoGrid({
            dataSource: {
                data: sampleData,
                schema: {
                    model: {
                        fields: {
                            name: { type: "string" },
                            date: { type: "date" }
                        }
                    }
                }
            },
            columns: [
                { field: "name", title: "Name" },
                { field: "date", title: "Date" , template: "#= formatDate(date) #"}
            ],
            filterable: true
        });

        $(document).ready(function() {
            $("th[data-title=Date]")
                .data("kendoFilterMenu")
                    .form.find("button[type=submit]")
                        .click(function(e) {
                            //gets filter type
                            var filterType = $(this.form).find($("select[data-role=dropdownlist]")).eq(0).val();
                            //if filter is "Is equal to"
                            if(filterType == "eq") {
                                e.preventDefault();
                                //gets the datePicker input date
                                var selectedDate = $(this.form).find($("input[data-role=datepicker]")).eq(0).val();
                                //create a filter
                                $("#grid").data("kendoGrid").dataSource.filter({
                                    field: "date",
                                    //create custom filter operator
                                    operator: function(fieldDate) {

                                        var parsedSelectedDate = kendo.parseDate(selectedDate);
                                        //parse the field date in order to ignore the time
                                        var parsedFieldDate = new Date(fieldDate.getFullYear(),  fieldDate.getMonth(), fieldDate.getDate());
                                        var result = (parsedFieldDate.getTime() == parsedSelectedDate.getTime());

                                        return result;

                                    },
                                    value: selectedDate
                                });
                                //close filter window
                                $("th[data-title=Date]").data("kendoFilterMenu").popup.close();
                            }
                            console.log("filter"); 
                        });
        });
    </script>
</body>
</html>