Correctly sorting multiple different date formats in a single column (date-time sorting plugin)
Correctly sorting multiple different date formats in a single column (date-time sorting plugin)
ProjectEphra
Posts: 5Questions: 2Answers: 0
Good day everyone,
I'm trying to put together a simple webpage for internal use at my workplace. I'm supposed to display a simple table from a database. One column of this table has dates in three different formats. I got them all to have the same format by using the render property as follows
{
data: "6",
render: function (data) {
var date = moment(data, [
"YYYYMMDD",
"DD.MM.YYYY",
"ddd MMM D HH:mm:ss ZZ YYYY",
])
.locale("de")
.format("dddd, Do MMMM YYYY");
if (date != "Invalid date") {
return date;
} else return "Installationsdatum unbekannt";
},
Now the problem is, datatables sorts this column as a bunch of strings. Of course I have googled before posting this question. I understand that I have to use the Ultimate date-time sorting plugin, which I did. I downloaded the plugin, included it on the page (after including moment and datatables ofc).
First I tried adding this line before initialzing the table, since this is the format I've converted to
$.fn.dataTable.moment("dddd, Do MMMM YYYY");
It did not work. Then I tried the following
$.fn.dataTable.moment([
"YYYYMMDD",
"DD.MM.YYYY",
"ddd MMM D HH:mm:ss ZZ YYYY",
]);
Essentially I passed an array of the different formats in this column before the tables has been initialized. Also made no difference.
I'm not sure what's wrong anymore.
Here's my entire home.js file
$(document).ready(function () {
$.fn.dataTable.moment([
"YYYYMMDD",
"DD.MM.YYYY",
"ddd MMM D HH:mm:ss ZZ YYYY",
]);
$("#table").DataTable({
initComplete: function () {
this.api()
.columns([0, 1])
.every(function () {
var column = this;
var select = null;
if (column[0][0] == 0) {
select = $(
'<select><option value="">Alle Server</option></select>'
);
} else if (column[0][0] == 1) {
select = $(
'<select><option value="">Alle Software</option></select>'
);
}
select
.appendTo($(column.header()).empty())
.on("change", function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search(val ? "^" + val + "$" : "", true, false)
.draw();
});
column
.data()
.unique()
.sort()
.each(function (d, j) {
select.append(
'<option value="' + d + '">' + d + "</option>"
);
});
});
},
orderCellsTop: true,
ajax: "../php/fetchData.php",
autoWidth: true,
processing: true,
lengthMenu: [
[500, 10, 25, 50, 75, 100, -1],
[500, 10, 25, 50, 75, 100, "All"],
],
language: {
url: "../locale/German.json",
},
columnDefs: [
{
targets: 4,
type: "datetime-moment",
},
],
columns: [
{
data: "2",
},
{
data: "3",
},
{
data: "4",
},
{
data: "5",
},
{
data: "6",
render: function (data) {
var date = moment(data, [
"YYYYMMDD",
"DD.MM.YYYY",
"ddd MMM D HH:mm:ss ZZ YYYY",
])
.locale("de")
.format("dddd, Do MMMM YYYY");
if (date != "Invalid date") {
return date;
} else return "Installationsdatum unbekannt";
},
},
],
});
});
I'm happy to provide any further detail that might be missing. General improvement suggestions to my script above are also more than welcome (I'm a newbie 
)
Thanks a lot for your time.
Cheers
This question has an accepted answers - jump to answer
Answers
My guess is the problem is with this:
else return "Installationsdatum unbekannt";You are mixing strings with your date format. This will cause Datatables to sort the column as a string.
You can use Orthogonal data to control what is used for sorting.
I didn't test this. There may be syntax or other errors. Its just meant to give you an idea of what you can do.
Kevin
Hi there @kthorngren. Thanks a lot for your time.
Unfortunately, this did not work properly. Something did change, since I now notice that the dates are somehow sorted, but incorrectly. Also moment.js throw a whole bunch of deprecation warnings in the developer console, that I could not quite understand. Here are the first few lines
I tried reading through the orthogonal data but couldn't get much out of it yet as I haven't read it thoroughly enough. I'll go back and read it again.
Thanks again
Not sure about the errors. Another person is posting a question similar to yours. See my example and explanation in this thread. Its a simplified version of yours but you could do something similar.
Kevin
Hi there again @kthorngren
Just wanted to post an update for anyone who might google for this in the future. I got the dates sorted correctly, without having to even use the plugin. The page you linked, orthogonal data, was great help. I did it as follows: I used the render option, defined _ (the fall back option) as "display". For display I used my old code, because I wanted the dates to display exactly like that, but in the render object, I defined a further option, namely "sort". I had copy-pasted the code from the "display" option, except I returned a different format "YYYYMMDD". You see, if you return this format, convert it to an integer, it will be in the correct order. Here's the code for those of you who would like that.
Only one thing is now not working quite correctly: the page shows a warning on each refresh: "Requested unknown parameter "6" for row 0 column 4.". Still couldn't figure out what's to it.
@kthorngren I have marked your last comment as an answer. Thanks so much.
Posting again because I accidentally deleted the post the first time.