Datatable sorting is not working for dd-mm-yyyy format

Question

I want to sort date on datatable. I want it to do in this format D-M-Y, but it doesn't work.

When I change the format to Y-M-D it works. But I need in this format D-M-Y.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script type="text/javascript">
       $(document).ready(function () {
           $('#est').dataTable({
               "bProcessing": true,
               "bServerSide": true,
               "sAjaxSource": '<?php echo base_url(); ?>index.php/welcome/tendersdatatable',
               "aaSorting": [
                   [3, "desc"]
               ],
               "bJQueryUI": true,
               "sPaginationType": "bootstrap",
               "iDisplayStart ": 20,
               "oLanguage": {},
               "fnInitComplete": function () {
                   //oTable.fnAdjustColumnSizing();
               },
               'fnServerData': function (sSource, aoData, fnCallback) {
                   $.ajax({
                       'dataType': 'json',
                       'type': 'POST',
                       'url': sSource,
                       'data': aoData,
                       'success': fnCallback
                   });
               }
           });
           $('.dataTables_filter input').addClass('form-control').attr('placeholder', 'Search...').css('margin-right', "4%");
           $('.dataTables_length select').addClass('form-control');
       });
</script>

Solution 1

This one worked for me if anyone need's it . https://datatables.net/forums/discussion/2467/need-help-for-sorting-date-with-dd-mm-yyyy-format

Solution 2

There is a plugin for date-time sorting. Source. It does have moment.js as a dependancy though.

$.fn.dataTable.moment( 'D-M-Y');

Solution 3

There are sorting plug-ins available, however none of them except datetime-moment supports the format you need. But datetime-moment plug-in has dependency with moment.js.

However it could be done by defining a custom sorting method date-dmy before you initialize your data table.

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

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

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

To use this custom type, you need to use aoColumnDefs to set desired column type as shown below. I'm using index 0 to set type of the first column. Other initialization options are omitted for simplicity.

$('#example').dataTable( {
    "aoColumnDefs": [
        { "sType": "date-dmy", "aTargets": [ 0 ] }
    ]
} );

Solution 4

You don't need a plugin. You can sort it adding a hidden element.

Convert the date to the format YYYYMMDD and prepend to the actual value (DD/MM/YYYY) in the , wrap it in an element, set style display:none; to the elements. Now the date sort will work as a normal sort. The same can be applied to date-time sort.

HTML

<table id="data-table">
   <tr>
     <td><span>YYYYMMDD</span>DD/MM/YYYY</td>
   </tr>
</table>

If you are using rails as me the sintaxys will be like this:

<td> 
    <span> 
        <%= youmodel.created_at.strftime("%Y%m%d") %> 
    </span> 
    <%= youmodel.created_at.strftime("%d/%m/%Y") %> 
</td>

CSS

#data-table span {
    display:none; 
}

View more solutions

Comments

[ziiweb]

Tanks, that is what I was looking for. I was trying using "dd-mm-YYYY" but it didn't work.