date with time and AM/PM is not filtering correctly
date with time and AM/PM is not filtering correctly
shellwe
Posts: 3Questions: 0Answers: 0
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.
This discussion has been closed.
Replies
Maybe the moment sorting plugin will help.
https://datatables.net/plug-ins/sorting/
Kevin
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?
Maybe you need to set the column to type
dateusingcolumnDefs. Something like this:columnDefs: [{ targets: [<date column number>], type: 'date'}]Kevin
The Moment plug-in should detect columns which match the format defined automatically and set the
columns.typeoption to the identifier for that sorter automatically. Thedatetype would use the built in date sorting, which only work reliably with ISO8601.I'm not sure what "tt" is? The Moment documentation shows
aorAfor am / pm (presumablyAfor capitalised form).Allan
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!
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