How to sort datatables with date in descending order

Question

I wish to show the records using datatables with default ordering based on one of my rows with date & time in descending order. Please help me in editing the jquery structure for that

Answer 1

The simpliest way is to add a hidden timestamp before the date in every TD tag of the column, for example:

<td class="sorting_1">
    <span style="display:none;">1547022615</span>09/01/2019  09:30
</td>

With the default string ordering, a timestamp would order the column the way you want and it will not be shown when rendered in the browser.

Answer 2

I had same problem. I used date-eu sorting plugin to sort dates in the format DD/MM/YY and I included the following JS file :

<script src="//cdn.datatables.net/plug-ins/1.10.11/sorting/date-eu.js" type="text/javascript"></script>

This worked for me.

$('#exemple').DataTable({
    "order": [[ 3, "desc" ]], //or asc 
    "columnDefs" : [{"targets":3, "type":"date-eu"}],
});

Read also this post on stackoverflow: Sorting date in datatable

Answer 3

I got the solution with the sorting of date. Just add type as 'date' and in targets, you have pass column number(count start from 0) with datatable options. And set 'order' with column number and type of format. See below code,

columnDefs: [ { type: 'date', 'targets': [4] } ],
order: [[ 4, 'desc' ]]

Answer 4

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 5

You can order by adding a dataset attribute in the record. Click here for details.

Example:

<td data-search="21st November 2016 21/11/2016" data-order="1479686400">
    21st November 2016
</td>

To show data sorted from the initial state define a column to look for sorting. For example:

$('#dataTable').DataTable({
    "order": [[10, 'desc']],
});

Answer 6

Just add "type":"date" directly in the columns like { "data": "MyDateField", "type":"date" }.

Answer 7

<td class="sorting_1">
    <span style="display:none;">201909010930</span>09/01/2019  09:30
</td>

Format your date in yyyyMMddHHmm. This will be your sortable timestamp. Then hide the formatted date using display none. This is actually a further explanation of the answer of joan16v

Answer 8

//working here code

$('#table').DataTable({
   columnDefs: [ { type: 'date', 'targets': [3] } ],
   order: [[ 3, 'desc' ]],          
});

Answer 9

I know this is an old thread. but you can basically use "aaSorting"

$('#exemple').DataTable({

    "aaSorting": [[3,'desc']],
});

Answer 10

This was the answer for me:

<td data-order=<fmt:formatDate pattern = "yyyy-MM-dd" value = "${myObject.myDate}" />>${myObject.myDate}</td>

more details, here in the html5 section: https://datatables.net/manual/data/

Answer 11

Perfect solution that I could implement is this:

1. If you generate data with AJAX at PHP file just make the line with date this way:

$rows[] =
    [
    "name" => $name,
    "date" => [
        "display" => $date, // Ex: '31.12.2020'
        "timestamp" => strtotime($date),    // Timestamp value for ordering, Ex: 1609372800
    ]
]

2. Then row would be output to JSON like this:

{
"name": "Vasya Pupkin",
"date": {
        "display": "31.12.2020",
        "timestamp": "1609372800"
    },
}

3. Finish by editing your JavaScript TadaTables object "date" column like this:

{
    "data": "date",
    render: {
        _: 'display',
        sort: 'timestamp'
    }
},

4. That's all! Now column with date is perfectly sorted.

Answer 12

This questions is quite old and most of the plugins mentioned in the answers have either been deprecated or stopped working (I've tried all).

Here is what works currently.

Add an extension:

$.fn.dataTable.ext.order['date-time'] = function (settings, col) {
    return this.api().column(col, { order: 'index' }).nodes().map(function (td, i) {
        var val = $(td).text().trim();    // Get datetime string from <td>
        return moment(val, "DD/MM/YYYY hh:mm:ss a").format("X"); 
    });
}

And then, for your data table:

$('#example').DataTable({
    "columns": [
        null,
        null,
        null,
        { "orderDataType": "date-time" }, // date-time is a custom key created in the above ext
        null,
        null,
        null,
        null,
        null,
        null
     ]
});

Update: You can simplify the above using:

$('#example').DataTable({
    "columnDefs": [
        { "orderDataType": "date-time", "targets": [3] }
    ]
});

The "targets": [] array can contain the indexes (from) of all the columns you want to apply the datetime sort to.

Note: I have used moment.js, you can use any other method of creating a valid date/datetime object. Also, the reference used is for the dom-sort plugin, therefore, the same method can be used for sorting with columns with complex dom structures as well.

Reference: https://datatables.net/examples/plug-ins/dom_sort

Answer 13

Try this, It works for me

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.10.21/sorting/datetime-moment.js"></script>
<script>
    $(document).ready(function () {
        $.fn.dataTable.moment( 'DD/MM/YYYY HH:mm' );
        $('#example').DataTable({"order": [[ 3, "desc" ]]});
    });
</script>

Answer 14

This worked for me. Don't forget to add moment to your code. I'm using node, so here is my import.

npm i moment -S

import moment from 'moment';

$('#example').DataTable({
    "order": [[ 3, "desc" ]], //or asc 
    "columnDefs" : [
       {
          targets: [3],
          render: function (data, type) {
             if (data !== null) {
                var wrapper = moment(new Date(data));
                return wrapper.format("MM/DD/YYYY h:mm:ss A");
             }
          }
       }
    ],
});

All credit to Ryan Besko. Answer found here: https://forums.asp.net/t/2154454.aspx?DataTables+Date+Time+sorting+DD+MM+YYYY+HH+mm+a

Answer 15

            Here the code:


           jQuery.extend(jQuery.fn.dataTableExt.oSort, {
             "date-uk-pre": function ( a ) {
              var ukDatea = a.split('-');
              return (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
           },


            "date-uk-asc": function ( a, b ) {
                return ((a < b) ? -1 : ((a > b) ? 1 : 0));
             },

            "date-uk-desc": function ( a, b ) {
               return ((a < b) ? 1 : ((a > b) ? -1 : 0));
              }
            }); 

Answer 16

As mentioned before date-eu.js library will work but for me it nedded a modification in the code:

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-eu-pre": function ( date ) {
    date = date.replace(" ", "");

    if ( !date ) {
        return 0;
    }

    var year;
    var eu_date = date.split(/[\.\-\/]/);


    if((eu_date[0] == undefined) || (eu_date[1] == undefined) || (eu_date[2] == undefined) ){
        eu_date[0] = 0;
        eu_date[1] = 0;
        eu_date[2] = 0;
    }

    //console.log(eu_date);

    /*year (optional)*/
    if ( eu_date[2] ) {
        year = eu_date[2];
    }
    else {
        year = 0;
    }

    /*month*/
    var month = eu_date[1];
    if ( month.length == 1 ) {
        month = 0+month;
    }

    /*day*/
    var day = eu_date[0];
    if ( day.length == 1 ) {
        day = 0+day;
    }

    return (year + month + day) * 1;
},

"date-eu-asc": function ( a, b ) {
    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},

"date-eu-desc": function ( a, b ) {
    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}

} );

Answer 17

This worked for me:

let options = {
  order: [[0, 'des']],
  columnDefs : [{"type":"date", "targets": 0}],
  ...
}

Answer 18

Default sorting in Datatables:

$(document).ready(function() { 
    $('#example').DataTable({ 
        "order": [[ 3, "desc" ]] 
    }); 
});

You can use asc for ascending order. And 3 means, 4th column is going to be ordered default.