Sorting date in datatable

Question

I'm trying to sort dates in my datatable like DD/MM/YYYY (day, month, year) . I was following https://datatables.net/plug-ins/sorting/ .

but all the date sorts seem to be deprecated and point to the datetime plugin: https://datatables.net/blog/2014-12-18

I don't seem to be able to get the datetime plugin working to sort. I tried the old way, with date. The initialize looks like this:

var historiektable = $('#dataTableHistoriek').DataTable({
    "paging" : false,
    "ordering" : true,
    "scrollCollapse" : true,
    "searching" : false,
    "columnDefs" : [{"targets":3, "type":"date"}],
    "bInfo": true
});

Without sorting it shows the table results like this:

When I put ordering:true 2 of the 2016 dates appear somewhere else in the list (so, not in order you would expect)

With everything pointing at Moment I thought I needed to sort with that. But I'm not sure how.

I saw $.fn.dataTable.moment('DD.MM.YYYY'); somewhere, but I understood that the fn doesn't work with this newest version of datatables anymore?

Anyone knows how to sort dates?

Answer 1

Use date-eu sorting plugin to sort dates in the format DD/MM/YY.

Include the following JS file //cdn.datatables.net/plug-ins/1.10.11/sorting/date-eu.js and use the code below:

var historiektable = $('#dataTableHistoriek').DataTable({
    "paging" : false,
    "ordering" : true,
    "scrollCollapse" : true,
    "searching" : false,
    "columnDefs" : [{"targets":3, "type":"date-eu"}],
    "bInfo": true
});

Answer 2

The example of Gyrocode.com seems the most effective. A recent solution says to use Moments.js but it does not work in my case. date-eu is deprecated by DataTables but it works perfectly.

If you want to sort by date and time using the date format dd/MM/yyyy HH:mm, use date-euro in the same way.

var table = $('#example-table').DataTable({
    columnDefs: [{ 'targets': 0, type: 'date-euro' }],
    order: [0, 'desc'],
});

For beginners, add the JS file date-euro to your site. Then add "columnDefs" in your code to indicate which column date-euro should be applied: targets = indicates the column containing the dates to sort, type = indicates the date-euro function to apply to the column. Finally, add "order" to apply the sort you want.

Answer 3

Please refer to this pen: https://codepen.io/arnulfolg/pen/MebVgx

It uses //cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js and //cdn.datatables.net/plug-ins/1.10.12/sorting/datetime-moment.js for sorting datatable

To sort the table by default use:

$.fn.dataTable.moment('DD/MM/YY');
$('#example').DataTable({ 
       "order": [[ 3, "desc" ]] 
    }); 

Answer 4

You can do your own comparator in order to keep the control of how is ordering the dates.

 jQuery.extend(jQuery.fn.dataTableExt.oSort, {
            "ddMmYyyy-pre": function (a) {
                a = a.split('/');
                if (a.length < 2) return 0;
                return Date.parse(a[0] + '/' + a[1] + '/' + a[2]);
            },
            "ddMmYyyy-asc": function (a, b) {
                return ((a < b) ? -1 : ((a > b) ? 1 : 0));
            },
            "ddMmYyyy-desc": function (a, b) {
                return ((a < b) ? 1 : ((a > b) ? -1 : 0));
            }
        });

As you can see in the above comparator you can choose how to parse the date depending on your data.

And in the columns definition:

  "columnDefs": [
            {
                targets: [4], type: "ddMmYyyy"
            }]

Answer 5

Following Plasebo's example works, but in my case the MySQL DATE_FORMAT was sorting on month value, not entire date. My solution was to remove the DATE_FORMAT from my SQL statement.

$(document).ready(function() {
  $.fn.dataTable.moment('DD/MM/YY');
  $('.happyTable').DataTable({
        "ordering": true,
        "order": [[ 1, "desc" ]],
  });
});

DATE_FORMAT(date,'%m/%d/%Y')

"2003-12-30 00:00:00" results in "12/30/2003" but sorts on month value.