Since HTML 5 is so much developed and almost all major browser supporting it. So now a much cleaner approach is to use HTML5 data attributes(maxx777 provided a PHP solution I am using the simple HTML). For non-numeric data as in our scenario, we can use data-sort or data-order attribute and assign a sortable value to it.
Zaheer Ahmed' solution works fine if you have to deal with already uk formated date.
I had an issue with this solution because I had to manage US formated date.
I figured it out with this tiny change :
function parseDate(a) {
var ukDatea = a.split('/');
return (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
}
jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-uk-pre": function ( a ) {
return parseDate(a);
},
"date-uk-asc": function ( a, b ) {
a = parseDate(a);
b = parseDate(b);
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},
"date-uk-desc": function ( a, b ) {
a = parseDate(a);
b = parseDate(b);
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});
Convert the date to the format YYYYMMDD and prepend to the actual value (DD/MM/YYYY) in the <td>, wrap it in an element, set style display:none; to the elements. Now the date sort will work as a normal sort. The same can be applied to date-time sort.
This solution is completely wrong. You can't convert a date to a number just adding each component of the date. If you try this logic for example with the following dates, you'll see it won't match correctly:
20/01/2014 = 2035
15/02/2014 = 2031
Witch date comes first, ascending? 20 of january? Not according to this logic :P
The correct way of doing the parsedate method is to convert the string to a valid datetime, and them use the getTime function to properly order the table.
var day = a.split('/')[0]
var month = a.split('/')[1]
var year = a.split('/')[2]
var date = new Date(month + "/" + day + "/" + year)
return date.getTime()
As stated here you need to include Moment.js and the datatable-moment plugin, then just declare the date format you are using.
The plugin will autodetect your date columns and sort it like it should be.
For moment.js format explanations, check here.
I know this is an old question and answers are old too. Recently I came across a simple and clean way of sorting dates. It can be done by HTML5 data-order attribute to <td> element.
Here's what I have done in my PHP:
<?php
$newdate = date('d M Y', $myDateTime); // Format in which I want to display
$dateOrder = date('Y-m-d', $myDateTime); // Sort Order
?>
<td data-order="<?php echo $dateOrder; ?>" >
<?php echo $newdate; ?>
</td>
$(document).ready(function() {
$.fn.dataTable.moment = function ( format, locale ) {
var types = $.fn.dataTable.ext.type;
// Add type detection
types.detect.unshift( function ( d ) {
return moment( d, format, locale, true ).isValid() ?
'moment-'+format :
null;
} );
// Add sorting method - use an integer for the sorting
types.order[ 'moment-'+format+'-pre' ] = function ( d ) {
return moment( d, format, locale, true ).unix();
};
};
$.fn.dataTable.moment('DD/MM/YYYY');
$('#example').DataTable();
});
the moment js works well for all date and time formats, add this snipper before you initialize the datatable like i've done earlier.
//Add this data order attribute to td
<td data-order="@item.CreatedOn.ToUnixTimeStamp()">
@item.CreatedOn
</td>
Add create this Date Time helper function
// #region Region
public static long ToUnixTimeStamp(this DateTime dateTime) {
TimeSpan timeSpan = (dateTime - new DateTime(1970, 1, 1, 0, 0, 0));
return (long)timeSpan.TotalSeconds;
}
#endregion
The problem here is that this example will sort entries from 1-st column like STRING but not like dates. If source code allows you to change date format from dd/mm/yyyy to yyyy/mm/dd "aaSorting" will work properly for you.
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);
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>");
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
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".
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.
$(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]);
}
})
});
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"}]
