Dates not sorting in DataTables

Question

Dates in my DataTables is not sorting the way it should? I am using AJAX, and here is the payload used:

{"data": [["5/24/2017","<a href='bulletin_edit.aspx?id=19'>0</a>","Uncategorized","Owners","New bulletin","No expiration","<button type='button' class='btn btn-xs btn-primary' onclick='archiveCircular(19);'>Archive</button>","<button type='button' class='btn btn-xs btn-info' onclick='showBBDetails(19);'>Show</button>"],["3/2/2016","<a href='bulletin_edit.aspx?id=13'>0</a>","Advisory","Everyone","New system update","No expiration","<button type='button' class='btn btn-xs btn-primary' onclick='archiveCircular(13);'>Archive</button>","<button type='button' class='btn btn-xs btn-info' onclick='showBBDetails(13);'>Show</button>"],["3/2/2016","<a href='bulletin_edit.aspx?id=12'>0</a>","Advisory","Everyone","First day of access","No expiration","<button type='button' class='btn btn-xs btn-primary' onclick='archiveCircular(12);'>Archive</button>","<button type='button' class='btn btn-xs btn-info' onclick='showBBDetails(12);'>Show</button>"],["11/4/2015","<a href='bulletin_edit.aspx?id=8'>0</a>","Advisory","Everyone","Door fixing in the lobby","No expiration","<button type='button' class='btn btn-xs btn-primary' onclick='archiveCircular(8);'>Archive</button>","<button type='button' class='btn btn-xs btn-info' onclick='showBBDetails(8);'>Show</button>"],["11/4/2015","<a href='bulletin_edit.aspx?id=7'>0</a>","Uncategorized","Everyone","Facade painting today","No expiration","<button type='button' class='btn btn-xs btn-primary' onclick='archiveCircular(7);'>Archive</button>","<button type='button' class='btn btn-xs btn-info' onclick='showBBDetails(7);'>Show</button>"],["5/4/2015","<a href='bulletin_edit.aspx?id=6'>0</a>","Advisory","Everyone","Repainting of Balconies","No expiration","<button type='button' class='btn btn-xs btn-primary' onclick='archiveCircular(6);'>Archive</button>","<button type='button' class='btn btn-xs btn-info' onclick='showBBDetails(6);'>Show</button>"],["5/4/2015","<a href='bulletin_edit.aspx?id=5'>0</a>","Uncategorized","Everyone","Insect Fuming again","No expiration","<button type='button' class='btn btn-xs btn-primary' onclick='archiveCircular(5);'>Archive</button>","<button type='button' class='btn btn-xs btn-info' onclick='showBBDetails(5);'>Show</button>"],["5/4/2015","<a href='bulletin_edit.aspx?id=4'>0</a>","Uncategorized","Everyone","Annual Meeting 2015","No expiration","<button type='button' class='btn btn-xs btn-primary' onclick='archiveCircular(4);'>Archive</button>","<button type='button' class='btn btn-xs btn-info' onclick='showBBDetails(4);'>Show</button>"]]}

The tables is rendered without issue. But, if I try to sort on the first date cell, it gets it all wrong (screenshots attached).

Here is the initialization code:

var uriActive = "ajax/bulletinpost/get_active.aspx";
tActive = $('#tblActive').DataTable({
    "ajax": uriActive,
    dom: 'Bfrtip',
    buttons: [{ extend: 'excelHtml5', title: 'Data export' }, { extend: 'pdfHtml5', title: 'Data export' }],
    "deferRender": true,
    "lengthMenu": [[50, 100, 150, 200, 250, -1], [50, 100, 150, 200, 250, "All"]],
    "iDisplayLength": 50,
    "order": [],
    "columnDefs": [{ "targets": 'no-sort', "orderable": false }, { "searchable": false, "targets": [1,6,7] }],
    "fnDrawCallback": function (oSettings) {
        $('[data-toggle="popover"]').popover({ 'trigger': 'hover', 'placement': 'top' });
        $('.editable').editable({ mode: 'popup' });
    }
});

Date format is mm/dd/yyyy. Anyone has any idea?

Did you try to use the the [`data-order` attribute](https://datatables.net/examples/advanced_init/html5-data-attributes.html)? If you store the unix timestamp of your date in there it should order fine. — Ivar, Dec 08 '17 at 19:22
Didn't try that, how would I get it in there? Could you give me an example using the payload (just one of the lines of course)? — Robert Benedetto, Dec 08 '17 at 19:24
If you check the link I added and there go to the HTML tab, you can `data-order="..."` on the "Start date" and "Salary" columns. In your case it's going to work a bit differently because you are using AJAX, but [this](https://stackoverflow.com/q/34081205/479156) post might help you there. — Ivar, Dec 08 '17 at 19:25
or just use a render returning `Date.parse( data.replace('/', '-') )` on the type `sort` .... — davidkonrad, Dec 08 '17 at 21:48
David, you think it is the / causing it? Could you provide an example based on my code above? — Robert Benedetto, Dec 09 '17 at 04:08
@RobertBenedetto, OK see below. There is really no need for `data-order` here, if you have many rows it will slow performance down. Should mostly be used when there is real discrepancy between the content of the cell and the way it should be sorted / filtered. Here you just have some dates in a localized format, not even an unusual format... — davidkonrad, Dec 09 '17 at 05:16

davidkonrad · Answer 1 · 2017-12-09T05:28:23.347

By default only strings which is parseable with Date.parse() will be recognized as dates. mm/dd/yyyy is not a parseable date format. A column with "odd" date formats in some form will need to perform the parsing itself, for example by using a plugin.

But there is really no hocus pocus about it. Just parse the date in a render callback and return the parsed dates as integer values :

"columnDefs": [
  { targets: 0, 
    type: 'num',
    render: function(data,type) {
      if (type == 'sort') return Date.parse( data.replace('/', '-').valueOf() )
      return data
    }
  },   
  { "targets": 'no-sort', "orderable": false }, 
  { "searchable": false, "targets": [1,6,7] }
],

Now the dates is sorted as they should -> http://jsfiddle.net/t34h6yuj/

NB: Obviously you should perform some kind of sanity check on data in the render callback. At least check if the server have returned a null value.

Dates not sorting in DataTables

1 Answers1