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

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

JimThomasJimThomas Posts: 6Questions: 2Answers: 0
edited March 2017 in Free community support

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • JimThomasJimThomas Posts: 6Questions: 2Answers: 0

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

    Have you tried providing moment with exactly that format?

  • JimThomasJimThomas Posts: 6Questions: 2Answers: 0

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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    Answer ✓

    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
                }
    
  • JimThomasJimThomas Posts: 6Questions: 2Answers: 0

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

    Thank You
    JIm

  • cife0001cife0001 Posts: 1Questions: 0Answers: 0
    edited July 2019

    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");
    }
    }

This discussion has been closed.