Sort datatable does not work for dd-mm-yyyy format
I want to sort date by 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 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>
This worked for me in case anyone needs it. https://datatables.net/forums/discussion/2467/need-help-for-sorting-date-with-dd-mm-yyyy-format
There is a plugin for sorting date and time. Source . However, it has the .js moment as a dependency.
$.fn.dataTable.moment( 'D-M-Y');
There is a sorting of plugins , however none of them except datetime-moment supports the format you need. But the plugin datetime-moment
has a dependency on the moment .js.
However, this can be done by defining a custom sort method date-dmy
before initializing 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 must use aoColumnDefs
to set the desired column type as shown below. I am using an index 0
to set the type of the first column. Other initialization parameters have been omitted for simplicity.
$('#example').dataTable( {
"aoColumnDefs": [
{ "sType": "date-dmy", "aTargets": [ 0 ] }
]
} );
You don't need a plugin. You can sort it by adding a hidden item.
Convert the date to YYYYMMDD format and add to the actual value (DD / MM / YYYY) in the field, wrap it in an element, set the display style: none; to the elements. The date sort will now work like a normal sort. The same can be applied to sorting date and time.
Html
<table id="data-table">
<tr>
<td><span>YYYYMMDD</span>DD/MM/YYYY</td>
</tr>
</table>
If you are using rails like me sintaxys would look 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;
}
This worked for me
jQuery.fn.dataTableExt.aTypes.unshift(
function (sData) {
if (sData !== null && sData.match(/^(0[1-9]|[12][0-9]|3[01])\/(0[1-9]|1[012])\/(19|20|21)\d\d$/)) {
return 'date-uk';
}
return null;
}
);
jQuery.extend(jQuery.fn.dataTableExt.oSort, {
"date-uk-pre": function (a) {
if (a == null || a == "") {
return 0;
}
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));
}
});