Jquery datatable Sort not working for Date Column?

Question

I Have a jquery datatable where date column format is Feb 16, 2018 but when it is sorted it is not getting sorted correctly.

I have used all date related column types mentioned Here

But Nothing seems to work. How can I resolve it?

Here is the code

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div class="panel-body btnsize">
  <table class="table table-striped table-bordered dttable" id="JsDataTable" style="border-radius: 17px 17px 0 0; border-style: solid; border-color: #fcfdfa;" width:100%;>
    <thead>
      <tr>
        <th style="width: 1px !important;" class="tblth">
          Sr
        </th>
        <th class="tblth" style="width:13% !important;">
          Date <i class="fa fa-fw fa-sort"></i>
        </th>
      </tr>
    </thead>
    <tbody class="dtbody tblth" style="color: #004D6B;">
    </tbody>
  </table>
</div>

var table = $("#JsDataTable").DataTable({
  scrollY: '50vh',
  scrollCollapse: true,
  "aaData": response,
  "pagingType": "full_numbers",
  "dom": '<"top"i>rt<"bottom"flp><"clear">',
  "sDom": 'Rfrtlip',
  "bInfo": true,
  "lengthMenu": [
    [10, 20, 30, -1],
    [10, 20, 30, "All"]
  ],
  "columnDefs": [{
    "searchable": false,
    "orderable": false,
    "targets": [0, 1, 2, 3, 4],
    "type": 'natural'
  }],
  "order": [
    [1, 'asc']
  ],
  "aoColumns": [{
      "mData": null
    },
    {
      "mData": "Date",
      'bSortable': true,
      "sType": "natural"
    },
  ],
  "searching": true,
  "paging": true,
  "bAutoWidth": false,
  "fixedColumns": false,
  //order: [],

});

Answer 1

This answer works: https://stackoverflow.com/a/25319252/1554947

        var table = $('#table');
        table = table.DataTable({
            columns: [
                { data: "link" },
                { data: "code" },
                { data: "entryDateTime" }
            ],
            columnDefs: [
                {
                    targets: 2,
                    render: function (data, type) {
                        if (type == "display") {
                            var date = new Date(data);
                            var options = { year: "numeric", month: "long", day: "numeric", hour: "numeric", minute: "numeric" };
                            return date.toLocaleDateString('tr-TR', options);
                        }

                        return data;
                    }
                }
            ],
            order: [[2, "desc"]] // default order when page loaded
        });

Answer 2

You can do it this way:

In this demo, I have taken one invisible field with ymdhis format of date and pass iDataSort with next invisible field so your date will be sorted with that field.

The iDataSort property is used for cases where you want one column to be sorted by the data contained in another column. This second column, will typically be hidden.

DEMO: https://codepen.io/creativedev/pen/OEgmdX

$(document).ready(function() {
    var dataSet = [
        ["Test1", "25 Apr 2011", "20110425"],
        ["Test2", "10 Feb 2011", "20110210"],
        ["Test3", "20 Apr 2012", "20120420"],
        ["Test4", "16 Feb 2018", "20180216"],
    ];
    var myTable;

    myTable = $('#example').DataTable({
        data: dataSet,
        "order": [
            [1, 'asc']
        ],
        "aoColumns": [null, {
            'iDataSort': 2
        }, {
            "bVisible": false
        }]
    });
});

Answer 3

The problem is probably in your columnDefs assignment.

"columnDefs": [{
     "searchable": false,
     "orderable": false,
     "targets": [0, 1, 2, 3, 4],
     "type": 'natural'
}],

You are using the term "type": 'natural', which means that when sorting is performed, it simply alphanumerically sorts the data.

For example, the date Dec 16, 2018 will actually be sorted as less than Feb 16, 2018, which you can see by doing simple string comparison.

"Dec 16, 2018" < "Feb 16, 2018" = true

Since you are using moment.js, you will want to adjust your columnDefs so that the columns that are DateTime formats, they will be given the assignment "type": "date".

"columnDefs": [
    //non-date fields
    {
        "searchable": false,
        "orderable": false,
        "targets": [0, 2, 3, 4],
        "type": 'natural'
     },
     //date-fields
     {
        "searchable": false,
        "orderable": true,
        "targets": 1,
        "type": 'date'
     }
],

Also, you do not need the aoColumns attribute. aoCoulumns is the legacy version of columnDefs. All the information in aoColumns is better expressed in the columnDefs attribute. In fact, providing both may also be contributing to your problems, as you are setting the column attributes one way, then changing the way it works through other means.

Here is a simple example of how to use the date type.

$(document).ready( function () {
  var table = $('#example').DataTable({
    columnDefs: [{
        "targets": 1,
        "type": 'date',
     }]
  });
} );

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>

<div class="container">
  <table id="example" class="display nowrap" width="100%">
    <thead>
      <tr>
        <th>id</th>
        <th>Date</th>
      </tr>
    </thead>
    <tfoot>
      <tr>
        <th>id</th>
        <th>Date</th>
      </tr>
    </tfoot>
    <tbody>
      <tr>
        <td>1</td>
        <td>Dec 16, 2018</td>
      </tr>
      <tr>
        <td>1</td>
        <td>Jan 16, 2018</td>
      </tr>
      <tr>
        <td>2</td>
        <td>Feb 16, 2018</td>
      </tr>
      <tr>
        <td>3</td>
        <td>June 16, 2018</td>
      </tr>
      <tr>
        <td>4</td>
        <td>June 16, 2017</td>
      </tr>
      <tr>
        <td>5</td>
        <td>Dec 16, 2016</td>
      </tr>
      <tr>
        <td>6</td>
        <td>Jan 16, 2016</td>
      </tr>
      <tr>
        <td>7</td>
        <td>Feb 16, 2016</td>
      </tr>
    </tbody>
  </table>
</div>