DateTime Column Sorting Ignores AM/PM
DateTime Column Sorting Ignores AM/PM
evermorian
Posts: 3Questions: 0Answers: 0
I have having a difficult time getting my tables to sort on a 12h format datetime column correctly. At best, it seems to be ignoring AM/PM. I have looked at a bunch of documentation, blog posts, and a number of other forum threads where datetime column sorting issues turned out to be incorrectly specifying the date format string or failing to set it in dataTable. I figure I am doing something wrong, but I am not having any luck finding the problem. Before giving up and adding a hidden column with the same value as a UNIX timestamp to sort on, I thought I would ask here.
I made a minimal demo of the issue.
Data:
{
"data": [
{
"DT_RowId": "row_54",
"time_entry": {
"entry_datetime": "2025-08-28 12:15 PM",
"billable_time": "1.00",
"non_billable_time": "0.00",
"fee": null,
"notes": "This should be the second entry."
}
},
{
"DT_RowId": "row_55",
"time_entry": {
"entry_datetime": "2025-08-28 11:00 AM",
"billable_time": "2.00",
"non_billable_time": "0.00",
"fee": null,
"notes": "This should be the third entry."
}
},
{
"DT_RowId": "row_56",
"time_entry": {
"entry_datetime": "2025-08-28 09:20 AM",
"billable_time": "1.00",
"non_billable_time": "0.00",
"fee": null,
"notes": "This fourth entry should appear at the bottom of the list."
}
},
{
"DT_RowId": "row_58",
"time_entry": {
"entry_datetime": "2025-08-28 02:00 PM",
"billable_time": "2.00",
"non_billable_time": "0.00",
"fee": null,
"notes": "This should be the first/most recent (top) item"
}
}
]
}
HTML:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="Date Sorting Issue">
<meta name="robots" content="noindex">
<meta name="author" content="Andrew Crawford">
<title>Date Sorting Issue</title>
<script
src="https://code.jquery.com/jquery-3.7.1.min.js"
integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo="
crossorigin="anonymous"></script>
<script
src="https://code.jquery.com/ui/1.14.1/jquery-ui.min.js"
integrity="sha256-AlTido85uXPlSyyaZNsjJXeCs07eSv3r43kyCVc8ChI="
crossorigin="anonymous"></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<script type="text/javascript" charset="utf-8" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.2/moment.min.js"></script>
<script src="https://cdn.datatables.net/v/bs5/jq-3.7.0/dt-2.3.3/datatables.min.js" integrity="sha384-TPbfnwPjOXla/xgRKrdEJc3CxsVpmUvo8H8FYUEdxCuZwGuinMchFsj6FCtCd5vU" crossorigin="anonymous"></script>
<script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/plug-ins/2.3.3/sorting/datetime-moment.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
<link href="https://cdn.datatables.net/v/bs5/jq-3.7.0/dt-2.3.3/datatables.min.css" rel="stylesheet" integrity="sha384-MsFmJuPKkTT2RH+sHl3CS88wA7fFK6JQIUcxUWLkizshdCyYQ1LB6560OTAn6SIo" crossorigin="anonymous">
</head>
<body>
<div class="col-lg-8 mx-auto p-4 py-md-5">
<main>
<h1 class="text-center"></h1>
<!-- Time Entries -->
<div class="d-flex bd-highlight mb-3">
<div class="p-2 bd-highlight">
<h2 class="pt-3 text-center">Date Sorting Issue</h2>
</div>
</div>
<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="time_entry" width="100%">
<thead>
<tr>
<th>Entry Date</th>
<th>Billable Time</th>
<th>Non-Billable Time</th>
<th>Fee</th>
<th>Notes</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Entry Date</th>
<th>Billable Time</th>
<th>Non-Billable Time</th>
<th>Fee</th>
<th>Notes</th>
</tr>
</tfoot>
</table>
<script>
(function($){
$(document).ready(function() {
$.fn.dataTable.moment( 'YYYY-MM-DD hh:mm A');
var table = new DataTable('#time_entry', {
fixedHeader: true,
retrieve: true,
ajax: 'junkdata.json',
columns: [
{
"data": "time_entry.entry_datetime",
"type": "datetime",
"format": "YYYY-MM-DD hh:mm A"
},
{
"data": "time_entry.billable_time",
"orderable": false
},
{
"data": "time_entry.non_billable_time",
"orderable": false
},
{
"data": "time_entry.fee",
"orderable": false
},
{
"data": "time_entry.notes",
render: function (data, type, row, meta) {
return data.replace(/(?:\r\n|\r|\n)/g, '<br>');
},
"orderable": false
}
],
order: [[0, 'desc']],
select: true,
lengthChange: false,
paging: false,
} );
} );
}(jQuery));
</script>
</main>
</div>
</body>
</html>
I have it up here (temporarily):
Replies
Remove that. You virtually never want to set
columns.typemanually. It overrides the automatic type detection and forces it incorrectly.Also change:
To be:
See this example.
Allan
Thank you, Allan!