Sorting after date doesn't work

medarob

I have a table which has a column for the date in the format "12/2018" (month/year).

Using the deprecated $.fn.dataTable.moment( 'MM/YYYY' ); notation worked. Correct sorting is possible.

Because I had an error described here: https://datatables.net/forums/discussion/77666/uncaught-typeerror-fn-datatable-moment-is-not-a-function I changed it to
DataTable.datetime('MM/YYYY');

Now the ASC/DESC arrows for sorting doesn't work correctly.

It seems that the value in the cell is considered as number because it sorts like:
12/2022, 12/2018, 11/2023, ... instead of 11/2023, 12/2022, 12/2018

Columns are
0: preview image
1: title
2: category
3: language
4: date
5: filesize

The Javascript looks like this:

$(document).ready(function () {

DataTable.datetime('MM/YYYY');

var tableID = $('table[id^="rds-upload-table-"]').attr('id');

// Uploads table with filter
$("#"+tableID).DataTable({
    dom: 'Pfrtip',
    language: {
        url: '/_assets/rds/Plugins/datatables/dataTables.2-3-2-i18n-de-DE.json'
    },
    searchPanes: {
        layout: 'columns-2', cascadePanes: true, i18n: {emptyMessage: '<i><b>Ohne Kategorie</b></i>'}
    },
    // sort by column #2: title ascending
    order: [[1, 'asc']],
    columnDefs: [{
        targets: 2, render: function (data, type, row) {
            if (type === 'sp') {
                //console.log(data.split(', '));
                return data.split(', ');
            }
            return data;
        }, searchPanes: {
            orthogonal: 'sp', show: true
        }
    }, {
        searchPanes: {
            show: true
        }, targets: [3]
    }, {
        searchPanes: {
            show: false
        }, targets: [0, 1, 4, 5]
    },]
});

});

I also found this example (https://datatables.net/examples/datetime/formatting-moment.html) but it did not work in my use case or I did comething wrong...

What do I have to change to make it work again with the new notation?

allan

I've just tried it here: https://live.datatables.net/xojinupa/1/edit and it appears to be working as expected.

Can you link to a test case showing the problem please?

Thanks,
Allan

medarob

I will try to add a similar testcase we have with other datatables options.

medarob

mh, I added the other options and also added more datatables dependencies which we have in use and it works: https://live.datatables.net/xojinupa/4/edit

If I edit out this line in the example

DataTable.datetime('MM/YYYY');

then the example behaves like our system... So something must be off in our system that it doesn't work? Can I check if setting the datetime() to "MM/YYYY" works?

medarob

Ok, I think I found the problem: The date is inside a <span> tag and then it's not working.

https://live.datatables.net/xojinupa/5/edit

Removing the <span> tag from the code worked.
I wonder why it had worked before with the old method.

Is it possible to remove tags via datatables? So in case another tag will appear in the <td></td> then the problem will reappear I think.

kthorngren

Your test case doesn't sort the dates correctly. It's due to having the dates wrapped in HTML. This is what you have:

<td><span>12/2018</span></td>

Datatables recognizes this as HTML not a date field so the sorting doesn't work.

Can I check if setting the datetime() to "MM/YYYY" works?

Yes, use initComplete and interrogate the settings variable passed into the function, something like this:

  initComplete: function ( settings ) {
    console.log( settings.aoColumns[ 4 ].sType )
  },

Here is your non-working example with the above code:
https://live.datatables.net/fiqucuye/1/edit

The output is html which won't sort the dates correctly. Here are a couple of choices:

1. Remove the span from the HTML table.
2. Use orthogonal data to remove the span tag from the sort and type operations.

I updated the above test case with the orthogonal data option:
https://live.datatables.net/xojinupa/7/edit

The output now is datetime-MM/YYYY which confirms the datetime format is applied to the column for sorting.

Kevin

medarob

Thank you @kthorngren and @allan