kendo-ui grid date format from MySQL sql query

Question

I have looked here for similar queries, and none quite answer my question, either in its entirety, or in a fashion I can understand.

I retrieve a string value from a MySQL database representing a datetime, i.e. "03 Oct, 2013 05:30:45PM" is represented as "20131003173045" in the database. This value is presented in a "Last Login" column in a Kendo Grid as a date. This is all contained within a PHP-oriented webpage using the MVC framework.

Previously I applied a date format in the SQL query to change the string from "20131003173045" to "03 Oct, 2013 05:30:45PM", but this gets presented in the KendoGrid as a string, meaning November Dates can appear before October dates, April dates come up first etc. This understandably is not the desired function, especially when trying to sort by this column.

Sample Code for the page rendering the KendoGrid:

<div class="div1">
    <div>
        <table id="listGrid"></table>
    </div>
</div>

<script>
    $(function() {
        $("#grid").kendoGrid({
            scrollable: false,
            groupable: false,
            sortable: {
                mode: "multiple"
            },
            resizable: true,
            pageable: true,
            dataSource:
            {
                transport:
                {
                    read:  {
                        url: "<?= $site_url ?>Settings/Users/List",
                        dataType: "json"
                    },
                    parameterMap: function (model, operation) {
                        if (operation == "read") {
                            return model;
                        }                            
                        else if(model) {
                            return { "json" : kendo.stringify(model) };
                        }
                        else
                            return "";
                    }                    
                },
                pageSize: 20,
                serverPaging: true,
                serverSorting: true,
                sort: { field: "LastLogin", dir: "desc" },
                serverGrouping: true,
                serverAggregates: false,
                schema: {
                  total: "total",
                  data: "data",
                  groups: "groups"
                }
            },

            toolbar: [ { title: "", template:'<a class="k-button k-button-icontext k-grid-add" href="<?= $site_url ?>Settings/Users/Add/">Add New User</a>' }],
            columns: [
                //{field: "Id", width: "40px"},
                {field: "UserName", title: "Alias", width: "160px"},
                {field: "ForeName", title: "Forename", width: "100px"},
                {field: "SurName", title: "Surname", width: "160px"},
                {field: "Initials", width: "80px"},
                {field: "CrmId", title: "CRM ID", width: "100px"},
                {field: "Dept", title: "Department", width: "100px"},
                {field: "Position"},
                // Below is the field in question
                {field: "LastLogin", title: "Last Login", width: "160px"}, 
                {field: "BlockedStatus", title: "Status", width: "90px"},
                { title: "", template:'<a class="k-button k-button-icontext k-grid-edit" href="<?= $site_url ?>Settings/Users/Update/#: Id #">Edit</a>' }
            ]
        });
    });
</script>

I've looked at the kendo.parseDate and kendo.toString, and combining the two, but can't seem to get anything to work; I either get "null" or "20131003173045", or the page doesn't load.

I can't change the database data, I can only format it as part of the SQL query, which is what I was doing initially.

I need the whole "20131003173045" string to become "03 Oct, 2013 05:30:45PM", and still be ordered in proper chronological order - how can I do this, and if the kendo.parseDate and kendo.toString are the correct tools to use, what am I doing wrong?

OnaBai OnaBai · Accepted Answer · 2013-10-03T19:59:34

What I suggest is implement a schema.parse function that converts that MySQL format to a Date object and then use columns.format for formatting it.

The Schema would be:

schema  : {
    data  : "data",
    total : "total",
    groups: "groups",
    parse : function (d) {
        $.each(d.data, function (idx, elem) {
            elem.LastLogin = kendo.parseDate(elem.LastLogin, "yyyyMMddHHmmss");
        });
        return d;
    }
}

Now, the columns definition for printing the date would define a format that is the one that you want.

{field: "LastLogin", title: "Last Login", width: "160px", format: "{0:dd MMM, yyyy hh:mm:sstt}" }},

kendo-ui grid date format from MySQL sql query

1 Answers