How to sort datatable desc order on "DD-MM-YYYY hh:mm a"

qasimidl · July 2022

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

Hi,

i am using SharePoint 2019 ( on prem ), with Jsom to retrieve and bind data

using bootstrap with datable to bind with html table, on dropdown change it retrives data to bind with table

Problem:
i have date column which contains index no 1 on datatable ,having format "DD-MM-YYYY hh:mm A".

I have applied sort but its not working on "DD-MM-YYYY hh:mm A", instead working fine on "MM-DD-YYYY hh:mm A".

Solution required: how can i sort it on desc order default on "DD-MM-YYYY hh:mm A" , then allow user to make it asc and desc on column click , as we do usual

Please guide
my code sample:

        datefinal = moment(oListItem.get_item('Created')).format('DD-MM-YYYY hh:mm');
            newRow = '<tr id=tr_' + oListItem.get_item('ID') + '> <td> <a href="#"  onclick="opendoc(' + oListItem.get_item('ID') + ",'DebtDocs','tblPrivatePlace'" + ');" >  ' + oListItem.get_item('FileLeafRef') + ' </a> </td>  <td>' + datefinal + '</td>

</tr>

        // $("#tblPrivatePlace").append(newRow);
        var table = $(tablename).DataTable();
        table.row("#tr_" + oListItem.get_item('ID')).remove().draw(false);
        ////

        var table = $(tablename).DataTable();
        table.row.add($(newRow)).draw();
        table.order([1, 'desc']).draw();

Thanks

rf1234 · July 2022

I recommend using this plugin for international date sorting:
https://datatables.net/plug-ins/sorting/datetime-moment

qasimidl · July 2022

no its not working, i have placed whole code,still hard luck to me

qasimidl · July 2022

code using jsom
$(document).ready(function () {

    if ($.fn.dataTable.isDataTable('#tblPrivatePlace')) {
        $('#tblPrivatePlace').DataTable();
    }
    else {
        $('#tblPrivatePlace').DataTable({
            "order": [[1, 'desc']],//order column in descending order.
            "columnDefs": [
                { "type": "date", "targets": [1] }//date column formatted like "03/23/2018 10:25:13 AM".
            ],
            "pageLength": 25
        });
    }

});

var oListItems, clientContext, collInvalidDoctype;

function RetrivePpDocs() {
    $('#tblPrivatePlace tbody').empty();
    $('#tblPrivatePlace').DataTable().clear();

    if ((parseInt($('#ddldoctypePp option:selected').text()) > 1) || $('#ddldoctypePp option:selected').text() != '---Select Document---') {
        oList = oWeb.get_lists().getByTitle("DebtDocs");
        var camlQueryGem = new SP.CamlQuery();
        {
            camlQueryGem.set_viewXml('<View><Query><Where><And><Eq><FieldRef Name=\'CompanyFk\' LookupId ="TRUE"  /><Value Type=\'number\'>' + parseInt(jQuery("#ddlcompanyPp").val()) + '</Value></Eq><And><Eq><FieldRef Name=\'DocTypeFk\' LookupId ="TRUE"  />' + '<Value Type=\'number\'>' + parseInt(jQuery("#ddldoctypePp").val()) + '</Value></Eq><Eq><FieldRef Name=\'IsPublic\'/> <Value Type=\'Number\'>2</Value></Eq></And></And></Where><OrderBy><FieldRef Name=ID Ascending="False"/></OrderBy></Query></View>');
        }

        oListItems = oList.getItems(camlQueryGem);
       clientContext.load(oListItems, 'Include(ID,DocTypeFk,DebtDocsComments, CompanyFk,Created,FileLeafRef,Status,Author)');         
        clientContext.executeQueryAsync(onsuccess, onfailed);
    }
}
function onsuccess() {

    var itemCount = oListItems.get_count();            
    var itemCount = oListItems.get_count();      
    var datefinal;
    var newRow;var tablename = 0;var status = 1; var statusNA = 3; var btns = ""; //var docurl = _spPageContextInfo.webAbsoluteUrl;
    var comments = "";

    if (collInvalidDoctype.get_count() > 0) {
        var oListItemNA = collInvalidDoctype.itemAt(0);
        statusNA = oListItemNA.get_item('Status');
    }       
    enableControlls(statusNA, "#btnnotapplicablePp", "#btnuploadPp");
    statusNA = 3;       
    for (var i = 0; i <= itemCount - 1; i++) {
        var oListItem = oListItems.itemAt(i);
        statusNA = 1;
        status = oListItem.get_item('Status') == 1 ? "Under Review" : oListItem.get_item('Status') == 2 ? "Accepted" : "Not Accepted";


        btns = Actionbtns(oListItem.get_item('ID'));
        datefinal = moment(oListItem.get_item('Created')).format('MMM-DD-YYYY hh:mm A');

        newRow = '<tr id=tr_' + oListItem.get_item('ID') + '> <td> <a href="#"  onclick="opendoc(' + oListItem.get_item('ID') + ",'DebtDocs','tblPrivatePlace'" + ');" >  ' + oListItem.get_item('FileLeafRef') + ' </a> </td>  <td data-sort="YYYYMMDD">' + datefinal + '</td>  <td> ' + status + '</td> <td> <a href="../../Pages/chatbot.aspx?dc=' + oListItem.get_item('ID') + '&dt=' + parseInt(jQuery("#ddldoctypePp").val()) + '&st=' + parseInt(jQuery("#ddlstagesPp").val()) + '&cm=' + parseInt(jQuery("#ddlcompanyPp").val()) + '&md=1' + '&ut=' + $("#hid_usertype").val() + '" target="_blank" id=link_"' + oListItem.get_item('ID') + '"> Comments </a> </td><td>' + oListItem.get_item('Author').$5c_1 + '</td> <td>' + btns + '</td></tr>';
        tablename = '#tblPrivatePlace';
        comments = "";

        var table = $(tablename).DataTable();
        table.row("#tr_" + oListItem.get_item('ID')).remove().draw(false);     
        var table = $(tablename).DataTable();
        table.row.add($(newRow)).draw();
        table.order([1, 'desc']).draw();             
        status = 1;
    }

kthorngren · July 2022

Did you add a statement like this to tell Datatables the datetime format?

$.fn.dataTable.moment( 'DD-MM-YYYY hh:mm A' );

If you have Datatables 1.12 then this support is built in. See this release note.

If you still need help please post a link to your page or a test case replicating the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

qasimidl · July 2022

$.fn.dataTable.moment( 'DD-MM-YYYY hh:mm A' ) where to add this ?
on document.ready ? below is load method

$(document).ready(function () {

    if ($.fn.dataTable.isDataTable('#tblPrivatePlace')) {
        $('#tblPrivatePlace').DataTable({
            "lengthMenu": [[25, 50, 100, -1], [25, 50, 100, "All"]],
            "pagingType": "simple_numbers",
            //destroy: true,
            "order": [[1, "desc"]], //or asc     
            "columnDefs": [{ "targets": 1, "type": "date-eu" }],
        });
    }
    else {
        $('#tblPrivatePlace').DataTable({
            "lengthMenu": [[25, 50, 100, -1], [25, 50, 100, "All"]],
            "pagingType": "simple_numbers",
            //destroy: true,
            "order": [[1, "desc"]], //or asc     
            "columnDefs": [{ "targets": 1, "type": "date-eu" }],
        });
    }

});

qasimidl · July 2022

below code is working to show data on desc order , based on "DD-MM-YYYY hh:mm A"
$(document).ready(function () {

    if ($.fn.dataTable.isDataTable('#tblGm')) {
        $('#tblGm').DataTable({
            destroy: true,
            "lengthMenu": [[25, 50, 100, -1], [25, 50, 100, "All"]],
            "pagingType": "simple_numbers",

            "order": [[2, "desc"]], //or asc     
            "columnDefs": [{ "targets": 2, "type": "date-eu" }],
        });
    }

});

Problem : when i click on column to sort, that's not working ,
what to do to let sort works ???

kthorngren · July 2022

$.fn.dataTable.moment( 'DD-MM-YYYY hh:mm A' ) where to add this ?

Add it just before you initialize your Datatable. Just like the example at the link rf1234 provided.

Kevin

qasimidl · July 2022

Is any one can help me out? , still issue not resolved

rf1234 · July 2022

It has been working fine for me on multiple occasions. I guess you should post a test case as per this forum's rules.

How can anyone help you out without seeing what your code is? Have you included the plugins (moment.js and the ultimate date sorting plugin)?

Using the plugin you need this code before you initialize the Datatable - as Kevin already mentioned above.

Here is an example from my own coding. I have different requirements depending on the page the user is on and depending on the user language. As you can see this is highly flexible.

//should be positioned after the number formatting to make sure
//date columns don't accidentally are assigned formatted-num
//set parms for date sorting using moment.js
if ( checkExists('#ctrLogPage')  || checkExists('#contractLogPage') ||
     checkExists('#authLogPage') || checkExists('#subLogPage')         ) {
    if (lang == 'de') {
        $.fn.dataTable.moment('DD.MM.YYYY, HH:mm:ss [Uhr]');
    } else {
        $.fn.dataTable.moment('DD/MM/YYYY, @ hh:mm:ss A');
    }
} else if ( checkExists('#contractGovPage') ) {
    if (lang == 'de') {
        $.fn.dataTable.moment('DD.MM.YYYY, HH:mm [Uhr]');
    } else {
        $.fn.dataTable.moment('DD/MM/YYYY, @ hh:mm A');
    }
}

allan · July 2022

Yes, simply saying it isn’t working isn’t going to give us the information that we need to be able to help you. A link to a test case showing the problem will give us the information we need to be able to help you.

Allan

How to sort datatable desc order on "DD-MM-YYYY hh:mm a"

How to sort datatable desc order on "DD-MM-YYYY hh:mm a"

Answers

Howdy, Stranger!

Categories

DataTables

How to sort datatable desc order on "DD-MM-YYYY hh:mm a"

How to sort datatable desc order on "DD-MM-YYYY hh:mm a"

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables