Datatables Date column sorting


Topic: Datatables Date column sorting

ozcanarslan pro asked 3 years ago

Hi everyone I am using datatables plugin and have issue with date column sorting. What i am doing is porting current working project with MDB Pro 4.7.3 to new rewrited project using MDB Admin Pro Template but date columns isnot sorting correctly. My date formad is Turkish date format like 22.03.2019.

With the new template date columns sorted as string not as date which means it is sorting first days then month and lastly year part.

Here is the datatable initialisation code:

jQuery.fn.dataTableExt.oSort['day-asc'] = function(a, b) {
    var daya = a.split('.');
    var dayb = b.split('.');

    var x = (daya[2] + daya[1] + daya[0]) * 1;
    var y = (dayb[2] + dayb[1] + dayb[0]) * 1;

    return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};

jQuery.fn.dataTableExt.oSort['day-desc'] = function(a, b) {
    var daya = a.split('.');
    var dayb = b.split('.');

    var x = (daya[2] + daya[1] + daya[0]) * 1;
    var y = (dayb[2] + dayb[1] + dayb[0]) * 1;

    return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};

 var table = $('#tblasansor').DataTable( {
        "dom": "<'row'<'col-12'B>><'row'<'col-md-5 col-12'l><'col-md-7 col-12'f>r><'table-responsive't><'row'<'col-md-5 col-12'i><'col-md-7 col-12'p>>",
        // dom: "BfrtiSp",
        "processing": true,
        "serverSide": true,
        // searching: true,
        // info: true,
        paging: false,
        "scrollY": "50vh",
        deferRender: true,
        // scrollY: 500,
        scrollX: true,
        scrollCollapse: true,
        responsive: true,
        autoWidth: false,
        "aaSorting": [[5, "desc"]],
        "pageLength": 10,
        'lengthMenu': [[10, 20, 25, 50, -1], [10, 20, 25, 50, 'All']],
        // "lengthMenu": [10, 15, 25, 50, 100, 250, 500, 1000],
        "pagingType": "full_numbers",
        "language": {
            "sProcessing": "İşleniyor...",
            "sLengthMenu": "Sayfada _MENU_ Kayıt Göster",
            "sZeroRecords": "Eşleşen Kayıt Bulunmadı",
            "sInfo": "  _TOTAL_ Kayıttan _START_ - _END_ Arası Kayıtlar",
            "sInfoEmpty": "Kayıt Yok",
            "sInfoFiltered": "( _MAX_ Kayıt İçerisinden Bulunan)",
            "sInfoPostFix": "",
            "sSearch": "Bul:",
            "sUrl": "",
            "oPaginate": {
                "sFirst": "İlk",
                "sPrevious": "Önceki",
                "sNext": "Sonraki",
                "sLast": "Son"
            },
            buttons: {
                excel: 'Excel`e Aktar',
                colvis: 'Düğme Gizle/Göster'
            }
        },
        buttons: [
            'excel',
            'colvis'
        ],
        columnDefs: [
            {
                "visible": true
            },
            {
                "targets": 7,
                "createdCell": function (td, cellData, rowData, row, col) {
                    if ( cellData == 'kirmizi' ) {
                        $(td).css('background-color', 'red');
                        $(td).css('color', 'white');
                        $(td).css('font-weight', 'bold');
                    }else if ( cellData == 'yesil' ) {
                        $(td).css('background-color', 'green');
                        $(td).css('color', 'white');
                        $(td).css('font-weight', 'bold');
                    }else if ( cellData == 'sari' ) {
                        $(td).css('background-color', 'yellow');
                        $(td).css('color', 'white');
                        $(td).css('font-weight', 'bold');
                    }else if ( cellData == 'mavi' ) {
                        $(td).css('background-color', 'blue');
                        $(td).css('color', 'white');
                        $(td).css('font-weight', 'bold');
                    }else {
                        $(td).css('background-color', 'grey')
                    }
                }
            },
            { type: 'turkish', targets: '_all' }
        ],
        "aoColumns": [
            /* # */ {"bSearchable": false, "bSortable": true},
            /* binaadı */ {"bSearchable": true, "bSortable": true},
            /* kimlikno */ {"bSearchable": true, "bSortable": true},
            /* ilçe */ {"bSearchable": true, "bSortable": true},
            /* eleman */ {"bSearchable": true, "bSortable": true},
            /* Kontrol Tarihi */ {"bSearchable": true, "bSortable": true, "sType": "day"},
            /* Kontrol Tipi */ {"bSearchable": true, "bSortable": true},
            /* sonuç */ {"bSearchable": true, "bSortable": true}
        ],
        scroller: {
            loadingIndicator: true
        },
        "ajax": "*************************************"
    } );
    // Setup - add a text input to each footer cell
    $('#tblasansor tfoot th').each(function () {
        var title = $(this).text();
        $(this).html('<input type="text" placeholder="' + title + '" />');
    });

    table.columns().every(function () {
        var that = this;

        $('input', this.footer()).on('keyup change', function () {
            if (that.search() !== this.value) {
                that
                    .search(this.value)
                    .draw();
            }
        });
    });
    $('#tblasansor tfoot tr').appendTo('#tblasansor thead');

Piotr Glejzer staff commented 3 years ago

Hi,
did you check this https://datatables.net/plug-ins/sorting/ ?
Best, Piotr


Ozcan Arslan pro commented 3 years ago

Hi, Yes. Before writing here i searched there first and tried as many as different solutions but nothing changed. Weird thing current live code with MDB 4.7.3 and Datatables 1.10.18 is working correctly and i am using the same real data, but not working in MDB Pro Admin Template.


Piotr Glejzer staff commented 3 years ago

Hmm, there should be the same code about data table in admin template like in our pro version. I will check this out.


Ozcan Arslan pro answered 3 years ago

Any update on this issue?


Piotr Glejzer staff answered 3 years ago

Hello. If you want to sort by for example for European date you have to add 'moment' plugin as they wrote about it here: https://datatables.net/blog/2014-12-18. For example here is my website with MDB Free and data table with sort example:
https://pglejzer.pl/datatableSort/

 <script src="http://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
  <script src="http://cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>

You need add this scripts below the main script with datatable js code

and something to initiate date with moment

<script>  
     $.fn.dataTable.moment('d.MM.YYYY');
        $('#dtMaterialDesignExample').DataTable();
</script>

Best,
Piotr


Ozcan Arslan pro commented 3 years ago

I tried moment to as Datatables guide redirect that solution now for datetime sorting but didn't work. My current live site is using MDB Pro and dtae sorting working correctly but the same exact code not working in MDB PRO Admin template!

Recently i tried 'Y-m-d' format and this time it is sorting correctly but it is the format we don't use in my country so many people will confuse.

Thank you for your research i will try again.


Please insert min. 20 characters.

FREE CONSULTATION

Hire our experts to build a dedicated project. We'll analyze your business requirements, for free.

Status

Answered

Specification of the issue
  • User: Pro
  • Premium support: No
  • Technology: MDB jQuery
  • MDB Version: 4.7.4
  • Device: Desktop
  • Browser: Firefox, Chrome
  • OS: Windows Server 2012
  • Provided sample code: No
  • Provided link: No