I am using a Jquery plugin called datatables
Its fantastic, however I cannot get the dates to sort correctly according to the dd/mm/yyyy format.
I have looked at their support formats but none of these fixes seem to work.
Can anybody here help me please?
This way it worked for me.
<td data-order="@item.CreatedOn.ToString("MMddyyyyHHmmss")">
@item.CreatedOn.ToString("dd-MM-yyyy hh:mm tt")
</td>
This date format in data-order attribute should be in this format which is being supported by DataTable.
If you don't want to use momentum.js or any other date formating, you can prepend a date format in milliseconds in the date value so that the sort will read according to it's millisecond. And hide the milliseconds date format.
Sample code:
var date = new Date();
var millisecond = Date.parse(date);
HTML
<td>'<span style="display: none;">' + millisecond + "</span>" + date + </td>
That's it.
You can resolve this issue with php.
$mydate = strtotime($startdate);
$newformat = date('d-m-Y',$mydate);
echo '<tr>';
echo ' <td data-sort="'. $mydate .'">'.$newformat .'</td>';
While there are so many answers to the question, I think data-sort works only if sorting is required in the "YYYYMMDD" and does not work while there is Hour / Minutes. The filter doesn't work properly while data-sort is used, at least I had that problem while trying in React JS.
The best solution in my opinion is to use data-order as the value can be provided dynamically for sorting purpose and format can be different while displaying. The solution is robust and works for any date formats including "DD/MM/YYYY HH:M".
For example:
<td data-order={obj.plainDateTime}>{this.formattedDisplayDate(obj.plainDateTime) }</td>
I found this solution from here - How do I sort by a hidden column in DataTables?
What seems to work for me was
push the full datetime object fetched with a select query from my db in a dataset wich will be draw by datatable format "2018-01-05 08:45:56"
then
$('#Table').DataTable({
data: dataset,
deferRender: 200,
destroy: true,
scrollY: false,
scrollCollapse: true,
scroller: true,
"order": [[2, "desc"]],
'columnDefs': [
{
'targets': 2,
'createdCell': function (td, cellData, rowData, row, col) {
var datestamp = new Date(cellData);
$(td).html(datestamp.getUTCDate() + '-' + (datestamp.getMonth()+1) + '-' + datestamp.getFullYear());
}
}
],
"initComplete": function(settings, json) {
$($.fn.dataTable.tables(true)).DataTable()
.columns.adjust();
}
});
Rows get sorted right , then I get a html I want in the row
Use the data-order attribute on the <td> tag like so (Ruby Example):
<td data order='<%=rentroll.decorate.date%>'><%=rentroll.decorate.date%></td>
Your decorator function here would be:
def date
object.date&.strftime("%d/%m/%Y")
end
If you get your dates from a database and do a for loop for each row and append it to a string to use in javascript to automagically populate datatables, it will need to look like this. Note that when using the hidden span trick, you need to account for the single digit numbers of the date like if its the 6th hour, you need to add a zero before it otherwise the span trick doesn't work in the sorting.. Example of code:
DateTime getDate2 = Convert.ToDateTime(row["date"]);
var hour = getDate2.Hour.ToString();
if (hour.Length == 1)
{
hour = "0" + hour;
}
var minutes = getDate2.Minute.ToString();
if (minutes.Length == 1)
{
minutes = "0" + minutes;
}
var year = getDate2.Year.ToString();
var month = getDate2.Month.ToString();
if (month.Length == 1)
{
month = "0" + month;
}
var day = getDate2.Day.ToString();
if (day.Length == 1)
{
day = "0" + day;
}
var dateForSorting = year + month + day + hour + minutes;
dataFromDatabase.Append("<span style=\u0022display:none;\u0022>" + dateForSorting +
</span>");
Try this:
"aoColumns": [
null,
null,
null,
null,
{"sType": "date"}, // "sType": "date" TO SPECIFY SORTING IS APPLICABLE ON DATE
null
]
To the column you want ordering keep "sType": "date-uk"
for example:-"data": "OrderDate", "sType": "date-uk"
After the completion of Datatable script in ajax keep the below code
jQuery.extend(jQuery.fn.dataTableExt.oSort, {
"date-uk-pre": function (a) {
var ukDatea = a.split('/');
return (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
},
"date-uk-asc": function (a, b) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},
"date-uk-desc": function (a, b) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});
Then You will get date as 22-10-2018 in this format
Problem source is datetime format.
Wrong samples: "MM-dd-yyyy H:mm","MM-dd-yyyy"
Correct sample: "MM-dd-yyyy HH:mm"
The simpliest way is to add a hidden timestamp before the date in every TD tag of the column, for example:
<td class="sorting_1">
<span class="d-none">1547022615</span>09/01/2019 09:30
</td>
With the default string ordering, a timestamp would order the column the way you want and it will not be shown when rendered in the browser.
Anyone struggling with UTC formats or others can read this
Suppose you have date in this format
Tue Oct 15 2019 08:41:35 GMT+0000 (UTC)
First we can convert it to millisecond using moment
For Example, in my case i was using HandleBar.js. So i created a Helper function to make it simpler
hbs.registerHelper('dateformat', function (datetime) {
return moment(datetime).valueOf(); })
or else
just convert it this way
moment("Tue Oct 15 2019 08:41:35 GMT+0000 (UTC)").valueOf();
once done just pass these values to your table
Now the trick here is to pass them both and hide the one in milliseconds and show the one in UTC format
<td >
<span class="hideThisDate">{{DATA IN MILLISECONDS}}</span>
{{YOUR DATE IN NORMAL FORMAT}}</td>
Now just simply hide the one in milliseconds through CSS
.hideThisDate {
display:none;
}
And you should be good to go!
I got the same issue while working with Doctrine. My datas was correctly sorted from the database with orderBy('p.updatedAt', 'DESC'), but when DataTable process them the final result was completly different.
The esiest way I found to resolve this is to add a hidden column timestamp in my table then order by this column when ordering by date. Full functional example here.
<table>
<thead>
<th>Some column<th>
<th>Visible date<th>
<th>Timestamp<th>
</thead>
<tbody>
<td>Example with Twig</td>
<td>{{ product.updatedAt ? time_diff(product.updatedAt) : '' }}</td>
<td>{{ product.updatedAt ? product.updatedAt.getTimestamp() : '' }}</td>
</tbody>
</table>
$(document).ready(function() {
let dateColumn = 1;
let timestampColumn = 2;
let currentColumn = timestampColumn;
let currentDirection = "desc";
let table = $("#dataTable").DataTable({
"order": [
[ timestampColumn, "desc" ],
// If you want to keep the default order from database
// just set "order": [] so DataTable wont define other order
],
"columnDefs": [
{
"targets": [ timestampColumn ],
"visible": false, // Hide the timestamp column
},
]
});
/**
* @var e: Events
* @var settings: DataTable settings
* @var ordArr: Current order used by DataTable
* example: [{"src":8,"col":8,"dir":"asc","index":0,"type":"string"}]
*/
table.on("order.dt", function (e, settings, ordArr) {
currentColumn = ordArr[0].col;
currentDirection = ordArr[0].dir;
if(currentColumn === dateColumn) {
table.order([timestampColumn, currentDirection]).draw();
// Without this line you'll get an unexpected behaviour
table.order([dateColumn, currentDirection]);
}
})
});
If someone has issues with server-side sorting, jsut make sure that visual column index corresponds to index from response
<thead>
<th>Column0</th>
<th>Column1</th>
<th>Column2</th>
<th>Column3</th>
</thead>
$columns = array(
array( 'db' => 'Column0', 'dt' => '0', 'field' => 'Column0' ),
array( 'db' => 'Column1', 'dt' => '4', 'field' => 'Column1' ),// looks ad 'dt' value
array( 'db' => 'Column2', 'dt' => '2', 'field' => 'Column2' ),
array( 'db' => 'Column3', 'dt' => '3', 'field' => 'Column3' ),
array( 'db' => 'HelperColumn', 'dt' => '1', 'field' => 'HelperColumn' )
);
In this case, if you click to sort column1 it will probably sort your table according to helperColumn in this case, since in the request index of the column is passet in order parameter order: [{column: "1", dir: "asc"}]
At least this was the issue for me