Sort DateTime field of a DataTable
Hello. I have the following DataTable:
success: function (data) {
$('#table-controle').html(data);
$('#table-controle').DataTable({
"language": {
"url": "../../../Content/json/Portuguese-Brasil.json"
},
"aaSorting": []
});
}
The data I already bring sorted correctly via procedure and load them by
$('#table-controle').html(data);
However, when you click on the label of a column of type DateTime, the data is ordered as a String and not as DateTime. Below are the example images:
How can I solve ?
4 answers
First of all, I would like to give you some advice so that you have more chances to get an answer, assemble a small Code Snippet that reproduces the error.
In your case, you are setting the aaSorting option to a array vazio, with the DataTable not defining let alone infering the type of each column.
So the mehor to do in this example is to omit aaSorting, even why this option should not be used in newer versions of DataTables, instead vc should use the DataTable - Options, in particular, the columns, columns.type, columnDefs and ordering
And finally, to order a string that has a date in the format dd/MM/yyyy it is necessary to use the plugin that references the columns.type data-eu, then set the column type to data-eu.
$(function () {
$("#sla").DataTable({
"columns": [
{ "type": "date-eu" },
{ "type": "date-eu" },
{ "type": "date-eu" },
null,
null
]
});
})<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet"/>
<link href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" rel="stylesheet"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.10.12/sorting/date-eu.js"></script>
<table id="sla" class="table table-striped table-hover">
<thead>
<tr>
<th>Data Problema</th>
<th>Data Previsão</th>
<th>Data Inclusão</th>
<th>Pendência</th>
<th>SLA</th>
</tr>
</thead>
<tbody>
<tr>
<td>10/07/2016</td>
<td>11/07/2016</td>
<td>10/07/2016</td>
<td>Operacional</td>
<td>1 hora</td>
</tr>
<tr>
<td>29/07/2016</td>
<td>29/07/2016</td>
<td>29/07/2016</td>
<td>Energia</td>
<td>4 horas</td>
</tr>
<tr>
<td>02/08/2016</td>
<td>02/08/2016</td>
<td>02/08/2016</td>
<td>Energia</td>
<td>4 horas</td>
</tr>
<tr>
<td>02/08/2016</td>
<td>30/08/2016</td>
<td>02/08/2016</td>
<td>Energia</td>
<td>4 horas</td>
</tr>
<tr>
<td>17/08/2016</td>
<td>18/08/2016</td>
<td>17/08/2016</td>
<td>Operacional</td>
<td>1 hora</td>
</tr>
</tbody>
</table>Complementing this answer, which is setting in specific fields, there is a way to identify the fields, and then do the sort:
1 - making jQuery Configuration:
jQuery.extend(jQuery.fn.dataTableExt.oSort, {
"date-br-pre": function ( a ) {
if (a == null || a == "") {
return 0;
}
var brDatea = a.split('/');
return (brDatea[2] + brDatea[1] + brDatea[0]) * 1;
},
"date-br-asc": function ( a, b ) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},
"date-br-desc": function ( a, b ) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});
2 - making Datatable Configuration:
$("#sla").DataTable({
columnDefs: [
{
type: 'date-br',
targets: 4
}
],
})
Source: Jrosset
You can use like this
To solve this problem, as stated in the reference, one must include the plugins
//cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js
//cdn.datatables.net/plug-ins/1.10.10/sorting/datetime-moment.js in the header, and then in javascript, include the following code:
$(document).ready(function() {
// você pode usar um dos dois com data ou data e hora
$.fn.dataTable.moment( 'DD/MM/YYYY HH:mm:ss' ); //Formatação com Hora
$.fn.dataTable.moment('DD/MM/YYYY'); //Formatação sem Hora
$('#IDTabela').dataTable({ //Criação da dataTable
//Campo ordenado por padrão (ao carregar página) O 1 é a coluna a ser ordenada lembrando que começa com 0
"order": [[1, "asc"]]
});
});
I hope I helped
It worked !!!
Add
<script type="text/javascript" language="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/plug-ins/1.10.10/sorting/datetime-moment.js"></script>And then, in javascript, include the following code:
$(document).ready(function() {
// você pode usar um dos dois com data ou data e hora
$.fn.dataTable.moment( 'DD/MM/YYYY HH:mm:ss' ); //Formatação com Hora
$.fn.dataTable.moment('DD/MM/YYYY'); //Formatação sem Hora