Sort Datatable Datetime Field

Question

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 Process and load them by $('#table-controle').html(data); However, when clicking on the label of a column of the type Datetime, the data are ordered as if they were String and not as Datetime. Below the sample images:

How can I solve ?

Answer 1

First of all, I’d like to give you a piece of advice so that you have a better chance of getting an answer, assemble a small one Code Snippet which reproduces the error.

In your case, you are setting the option aaSorting as a array vazio, with Datatable is not defining and much less inferring the type of each column.

So the best thing to do in this example is to omit the aaSorting, even why this option should not be used in newer versions of DataTables, in its place you must use the DataTable - Options, in particular the columns, columns.type, columnDefs and ordering

and finally, to sort a string that has a date in the format dd/MM/yyyy it is necessary to use the plugin which reference 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>

Answer 2

Complementing this answer, which is setting in specific fields, there is a way of identifying the fields, to then do the ordering:

1 - Setting up Jquery:

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 - Configuring the Datatable:

$("#sla").DataTable({
    columnDefs: [
        {
            type: 'date-br', 
            targets: 4 
        }
    ],
})

Source: Jrosseto

Answer 3

you can use it like this

To solve this problem, as stated in the reference, 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’ve helped

Answer 4

People will contribute in a very practical way.

I created an ordemData (SQL row_number) and include <p hidden> OrdemData</p> within the <td> of the date in the table.

Answer 5

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