0

Datatable sorting with all things excluding with Dates. Only sort with date(days) without considering their months. I have dates in (DD-MM-YYYY) formats which was coming dynamically from database. But some of the dates were coming between another month also.

I have used Jquery (jquery-3.3.1.js) and Datatable (datatables_1.10.19.js)

$(document).ready(function (){
    var rows_selected = [];
    var bookid_value = [];
    var table = $('#example').DataTable({
        "language": {
            "search": ' ',
            "searchPlaceholder": "Search",
        },
        lengthChange: false,
        "scrollY":        "1000px",
        "scrollCollapse": true,
        "paging":         false,
        'columnDefs': [{
            'targets': 1,
            'searchable': true,
            'orderable': false,
            'width': '1%',
            'bSort': true,
            "type": 'date'
        }],
        'order': [[1, 'asc']],

        'rowCallback': function(row, data, dataIndex){
             var rowId = data[0];
            if($.inArray(rowId, rows_selected) !== -1){

                $(row).find('input[type="checkbox"]').prop('checked', true);
                $(row).addClass('selected');
            }
        }
    });

});

Output : (After Sorting) 

31-08-2019
31-07-2019
31-08-2019
25-07-2019
31-08-2019
08-07-2019
31-08-2019
04-07-2019
31-08-2019
10-07-2019
10-07-2019
13-07-2019
15-07-2019
31-08-2019
31-08-2019
Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
nkumar
  • 79
  • 2
  • 3
  • 14
  • Your date format is not recognized by datatables. you can google for datatable sort dates and you will realize that: these dates are managed as strings and not dates. So this sorting is correct (for a string). – Lelio Faieta Jul 02 '19 at 08:07
  • Possible duplicate of [Datatable date sorting dd/mm/yyyy issue](https://stackoverflow.com/questions/12003222/datatable-date-sorting-dd-mm-yyyy-issue) – Lelio Faieta Jul 02 '19 at 08:07
  • This works for me https://stackoverflow.com/questions/36984554/sorting-date-in-datatable/76356287#76356287 – Pushpak May 29 '23 at 09:59

2 Answers2

1

Try using moment.js plugin

$(document).ready(function (){
    var rows_selected = [];
    var bookid_value = [];

    $.fn.dataTable.moment('DD-MM-YYYY');

    var table = $('#example').DataTable({
        "language": {
            "search": ' ',
            "searchPlaceholder": "Search",
        },
        lengthChange: false,
        "scrollY":        "1000px",
        "scrollCollapse": true,
        "paging":         false,
        'columnDefs': [{
            'targets': 1,
            'searchable': true,
            'orderable': false,
            'width': '1%',
            'bSort': true,
            "type": 'date'
        }],
        'order': [[1, 'asc']],

        'rowCallback': function(row, data, dataIndex){
             var rowId = data[0];
            if($.inArray(rowId, rows_selected) !== -1){

                $(row).find('input[type="checkbox"]').prop('checked', true);
                $(row).addClass('selected');
            }
        }
    });

});

More info can be found here

Alex
  • 878
  • 1
  • 10
  • 25
0

As far as I know, DataTable (JS) sorts independent of your query. I share my solution using JS. In my case the first column, the number 0 is Date type and It's that I want to order by default.

JavaScript

$('#datatable').DataTable({
order: [[0, 'desc']],
responsive: true
});

View (HTML) Illustrative example.

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css"/>
<script type="text/javascript" src="https://cdn.datatables.net/v/bs4/dt-1.10.18/r-2.2.2/datatables.min.js"></script>
<table id="datatable" class="table table-striped table-bordered" style="width:100%">
<thead>
    <tr>
    <th>Date</th>
    <th>Name</th>
    <th>Option</th>
    </tr>
</thead>
<tbody>
    <tr>
    <td>datevalue</td>
    <td>namevalue</td>
    <td>optionvalue</td>
    </tr>
</tbody>
<tfoot>
    <tr>
    <th>Date</th>
    <th>Name</th>
    <th>Option</th>
    </tr>
</tfoot>
</table>

Source: https://datatables.net

Regards

Gamaliel
  • 455
  • 6
  • 5