Date sorting is not working when formatting. JSON Result and REST API Call.

JimThomas

Here is my code.

        $(document).ready(function () {
            //var url = _spPageContextInfo.webAbsoluteUrl;
            var table = $('#example').DataTable({
                "order": [[ 4, "desc" ]],
                "ajax": {
                    "type": "GET",
                    "url": "http://rlls2013dev/_api/web/lists/getbytitle('ISS')/items?$select=ID,tti_ISS_Number,tti_Status,Created,LinkeduserID/tti_First_Name,LinkeduserID/tti_Last_Name&$expand=LinkeduserID/tti_First_Name,LinkeduserID/tti_Last_Name&$top=1000",
                    "dataType": "json",
                    "headers": {
                        "Accept": "application/json;odata=verbose"
                    },
                    "dataSrc": function (json) {
                        //Make your callback here.
                        return json.d.results;
                    }
                },
                "lengthChange": false,
                "iDisplayLength": 30,
                "columns": [
                    {
                        "data": null,
                        "orderable": false,
                        "sortable": false,
                        "className": "js-showVersions expandIcon",
                        "defaultContent": ''
                    },
                    { "data": "tti_ISS_Number" },
                    {
                        "data": "LinkeduserID.tti_First_Name",
                        "render": function (data, type, row) {
                            return data + ' ' + row.LinkeduserID.tti_Last_Name;
                        },
                        "targets": 0
                    },
                    { "data": "tti_Status" },
                    {
                        "data": "Created",
                        "render": function (data, type, row) {
                            return moment(new Date(data).toString()).format('DD/MM/YYYY HH:mm');
                        }
                    },
                    {
                        "data": "ID",
                        "visible": false
                    }
                ]
            });

Sorting working when not using render to return formatted date.
Added ultimate plugin and added below line did not show any effect.
$.fn.dataTable.moment( 'dddd, MMMM Do, YYYY' );

Here is the JSON Result date format "2016-12-29T19:06:17Z" and sorting is working when no formatting done in render section.

Here is the table

<table id="example" class="table table-bordred table-striped table-hover">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>ISS Number</th>
                    <th>Trip Lead</th>
                    <th>Status</th>
                    <th>Created</th>
                </tr>
            </thead>
            
            <tfoot></tfoot>
        </table>

My rows are dynamically generated. Read multiple threads saying to use data-order. Could someone guide me on how to use that since table data coming from json result.

Thank You
Jim

allan

Hi Jim,

You say you tried:

$.fn.dataTable.moment( 'dddd, MMMM Do, YYYY' );

But in your code you are using the date format 'DD/MM/YYYY HH:mm'.

Did you try:

$.fn.dataTable.moment( 'DD/MM/YYYY HH:mm' );

If that doesn't help, we'd need a link to a test case please.

Allan

JimThomas

Hi Allan,

I tried both ways,

Since my page resides inside share point, no way to give a link to page. Instead, here is the json datasource.

https://api.myjson.com/bins/1hi3lv

I am looking to sort the date column.

Thank You
Jim

tangerine

Here is the JSON Result date format "2016-12-29T19:06:17Z"

Have you tried providing moment with exactly that format?

JimThomas

When I tried with YYYY/MM/DD HH:mm sorting working. But our business need grid shows DD/MM/YYYY HH:mm

tangerine

Oh, okay. I usually use a column.render function to show the format required for display (col A), and a hidden column with the original datetime data (col B ). Then use B to sort A.
From my own code, columns settings:

           {
            // Sort column 5 (last_update) using data from column 6 (date_sort).
                data: "last_update",
                orderData: [6]
            },
            {
                data: "date_sort",
                visible: false,
                searchable: false
            }

JimThomas

Thank You @tangerine . That did the magic. Thanks a lot.

Thank You
JIm

cife0001

Hi, you can try configuring the following

{"data": "last_update",
type : "date", // for ordering
render: function(data, type, row){ // for visualizarion format
if(type === "sort" || type === "type"){
return data;
}
return moment(data).format("DD-MM-YYYY HH:mm:ss");
}
}