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"

qasimidlqasimidl Posts: 6Questions: 1Answers: 0

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

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

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

  • qasimidlqasimidl Posts: 6Questions: 1Answers: 0

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

  • qasimidlqasimidl Posts: 6Questions: 1Answers: 0

    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;
        }
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    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

  • qasimidlqasimidl Posts: 6Questions: 1Answers: 0

    $.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" }],
            });
        }
    
    });
    
  • qasimidlqasimidl Posts: 6Questions: 1Answers: 0

    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 ???

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    edited 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

  • qasimidlqasimidl Posts: 6Questions: 1Answers: 0

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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');
        }
    }        
    
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

Sign In or Register to comment.