Sorting after date doesn't work

Sorting after date doesn't work

medarobmedarob Posts: 17Questions: 4Answers: 0

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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 64,675Questions: 1Answers: 10,695 Site admin

    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

  • medarobmedarob Posts: 17Questions: 4Answers: 0
    edited July 5

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

  • medarobmedarob Posts: 17Questions: 4Answers: 0
    edited July 5

    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?

  • medarobmedarob Posts: 17Questions: 4Answers: 0

    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. :smile:
    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.

  • kthorngrenkthorngren Posts: 22,103Questions: 26Answers: 5,097
    Answer ✓

    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

  • medarobmedarob Posts: 17Questions: 4Answers: 0

    Thank you @kthorngren and @allan :smile:

Sign In or Register to comment.