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

Question

0.00/5 (No votes)

See more:

SharePoint2016

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 

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


Code Sample :

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

// $("#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();
Please guide

Thanks

What I have tried:

applied on load not working like document.ready()

Posted 30-Jun-22 19:48pm

qasimidl

Updated 30-Jun-22 21:53pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2022-06-30T20:06:00

Solution 1

Check your data: if it's string based then all comparison is string as well: which means that the whole comparison is based on the first pair of different characters encountered. So 01-01-2022 is before 10-12-1944 and so on.

To get any real sort order, the data loaded into the DataTable needs to be stored as Date based information, not string - then the display format should be irrelevant.

Posted 30-Jun-22 20:06pm

OriginalGriff

Comments

qasimidl 1-Jul-22 2:43am

give me code or any relevant example to get it done please

OriginalGriff 1-Jul-22 3:22am

How do you expect anyone to do that when we have no idea where your data comes from, how you are loading it, or what the heck you do with it after that?

qasimidl 1-Jul-22 6:41am

here u go
$(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()) + '<and><eq><fieldref name="\'DocTypeFk\'" lookupid="TRUE">' + '<value type="\'number\'">' + parseInt(jQuery("#ddldoctypePp").val()) + '<eq><fieldref name="\'IsPublic\'/"> <value type="\'Number\'">2<orderby><fieldref name="ID" ascending="False">');
}

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 = ' ' + oListItem.get_item('FileLeafRef') + ' ' + datefinal + ' ' + status + ' Comments ' + oListItem.get_item('Author').$5c_1 + ' ' + btns + '';
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;
}

qasimidl 4-Jul-22 2:42am

any suggestion please