CODEWITHSUNDEEP
datatables
Robert Benedetto
Robert Benedetto

Reputation: 1710

Dates not sorting in DataTables

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?

Upvotes: 0

Views: 147

Answers (1)

davidkonrad
davidkonrad

Reputation: 85538

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.

Upvotes: 2

Related Questions