date with time and AM/PM is not filtering correctly

shellwe

We are formatting our dates like "MM/dd/yyyy hh:mm:ss tt" but it seems to ignore the PM and AM and just sorts them by the numbers.
So for example:
04/11/2017 01:35:51 PM
Is before:
04/11/2017 11:30:44 AM
When sorting ascending because it sees 11 as higher than 1.

Our function is this:
$(document).ready(function () {
$('#MyAdminList').DataTable({
"order": [[1, "asc"]]
});
});

We just downloaded Datatables last month so we have the latest to whatever that version is.

kthorngren

Maybe the moment sorting plugin will help.
https://datatables.net/plug-ins/sorting/

Kevin

shellwe

Kthorngren, thank you for your reply!

Your link lead me to this page:
https://datatables.net/blog/2014-12-18

I downloaded moment.min.js and datetime-moment.js and put them into my project. I attempted the following code:

$(document).ready(function() {
$.fn.dataTable.moment('MM/dd/yyyy hh:mm:ss tt');
$('#MyAdminList').DataTable();
});

But the sorting still didn't work. I pulled that Date/Time formatting from a C# file and maybe something like "03/08/2017 01:37:59 PM" would have a different Date/Time format in JS? Either that or perhaps I implemented it incorrectly?

kthorngren

Maybe you need to set the column to type date using columnDefs. Something like this:
columnDefs: [{ targets: [<date column number>], type: 'date'}]

Kevin

allan

The Moment plug-in should detect columns which match the format defined automatically and set the columns.type option to the identifier for that sorter automatically. The date type would use the built in date sorting, which only work reliably with ISO8601.

"MM/dd/yyyy hh:mm:ss tt"

I'm not sure what "tt" is? The Moment documentation shows a or A for am / pm (presumably A for capitalised form).

Allan

shellwe

Allan and kthorngren, thank you for responding to me. I decided to put in date because not every date field has a value in it so I can see how it may get confused. It looks like it is working now so I ended up with this.

$(document).ready(function () {
$.fn.dataTable.moment('MM/dd/yyyy hh:mm:ss A');
$('#MyAdminList').DataTable({
"order": [[1, "asc"]],
columnDefs: [{ targets: [4, 5], type: 'date'}]
});
});

Going to send it through and will be back on here if QA finds any issues.

Thank you for all of your help!

allan

Make sure they test it in browser's other than Chrome. There should never be any need to set type: 'date' and in fact I think it will be harmful to do so.

Allan