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: [],

});

How do we sort different date formats in jquery datatables specially this one (**Feb 16, 2018**) in my case? — , Jun 13 '18 at 11:42
you can use data-order and pass ymdHis format in that or use visible field — Bhumi Shah, Jun 13 '18 at 11:44
What SortType I should pass if I render the date from moment.js? — , Jun 13 '18 at 11:59

Rich · Answer 1 · 2018-06-13T13:44:55.140

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>

simple and straight and logically correct. thanks. I used it as a part of the columns:[{"data": "dateEstb", "type":"date" }] followed by "order":[[9, "desc"]]. Basically it says the column is a date type and the sorting is applied based on the type. — Uresh K, Jan 08 '20 at 22:26

score 1 · Accepted Answer · answered Jun 14 '18 at 03:54

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

I agree that knowing that you can sort one column based on another can be useful, but in this case, passing down the same data in a different format as an additional column seems very inefficient, especially for large data sets. — Rich, Jun 14 '18 at 12:51

score 0 · Answer 3 · answered Oct 06 '21 at 09:20

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

Jquery datatable Sort not working for Date Column?

3 Answers3

Linked