Datetime plugin doesn't sort in Datatable

Question

I can't get the ultimate plugin (https://datatables.net/blog/2014-12-18) work for my site. I have a datetime column. The input for the column (called eisodos) from JSON is dd/MM/yyyy HH:mm:ss (e.g. 31/10/2018 10:03:00)

The field in SQL Server is Datetime. In query.php, I FORMAT it to dd/MM/yyyy HH:mm:ss and encode it to JSON.

If I FORMAT it to yyyy/MM/dd HH:mm:ss it sorts fine in the Datatable plugin, but I want it to be displayed like dd/MM/yyyy HH:mm:ss.

I have include both scripts (latest versions) :

<script src="bower_components/moment/min/moment.min.js"></script>

and

<script src="bower_components/moment/min/datetime-moment.js"></script>

I have checked everything.

The result I get when I sort is like this:

I have been searching two days and I can't find a solution.

Here is my code:

$(function ()
{
    $.fn.dataTable.moment('dd/MM/yyyy HH:mm:ss');

    var table = $('#example').DataTable(
    {
        ajax: {url: "query.php", dataType: "json", dataSrc: ''},
        "columns": [
            {"data": "eisodos"}
            // I have also tried the following (column render) but nothing changed.
            // "render": function(data, type, full) {return moment(data).format('dd/MM/yyyy HH:mm:ss');}   
        ],
        "language": {"url": "Greek.json"}
    });
});

Answer 1

In your case, you will have to use the argument type of the render method for the column. For read further about this, refer to Columns Render. Basically, this arguments lets you format your column data for differents types of actions (sort, diplay, etc...). In your case, you will need to do something like this:

$(function ()
{
    $.fn.dataTable.moment('dd/MM/yyyy HH:mm:ss');

    var table = $('#example').DataTable(
    {    
        ajax: {url: "query.php", dataType: "json", dataSrc: ''},
        "columns": [
        {
            "data": "eisodos",
            "render": function(data, type, full)
            {
                if (type == 'display')
                    return moment(data).format('dd/MM/yyyy HH:mm:ss');
                else
                    return moment(data).format('yyyy/MM/dd HH:mm:ss');
            }
        }],
        "language": {"url": "Greek.json"}    
    });
});

On the previous code, date will be formated to the spanish way when the action is for display, and for others actions (like sorting) the english format will be used. You can also refer to this answer I have made before for a similar issue:

Sort numeric data.table values that are formatted