I am using the datatables jquery plugin and want to sorty by dates.
I know they got a plugin but I can't find where to actually download it from
http://datatables.net/plug-ins/sorting
I believe I need this file: dataTables.numericComma.js yet I can't find it anywhere and when I download datatables it does not seem to be in the zip file.
I am also not sure if I need to make my own custom date sorter to pass into this plugin.
I am trying to sort this format MM/DD/YYYY HH:MM TT(AM |PM)
Thanks
Edit
How can I change this to sort by MM/DD/YYYY HH:MM TT(AM |PM) and change it to U.S date?
jQuery.fn.dataTableExt.oSort['uk_date-asc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');
var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};
jQuery.fn.dataTableExt.oSort['uk_date-desc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');
var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};
This question is related to
jquery
jquery-plugins
datatables
I got solution after working whole day on it. It is little hacky solution Added span inside td tag
<td><span><%= item.StartICDate %></span></td>.
Date format which Im using is dd/MM/YYYY. Tested in Datatables1.9.0
Datatables only can order by DateTime in "ISO-8601" format, so you have to convert your date in "date-order" to this format (example using Razor):
<td data-sort="@myDate.ToString("o")">@myDate.ToShortDateString() - @myDate.ToShortTimeString()</td>
Follow the link https://datatables.net/blog/2014-12-18
A very easy way to integrate ordering by date.
<script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>
Put this code in before initializing the datatable:
$(document).ready(function () {
// ......
$.fn.dataTable.moment('DD-MMM-YY HH:mm:ss');
$.fn.dataTable.moment('DD.MM.YYYY HH:mm:ss');
// And any format you need
}
As of 2015, the most convenient way according to me to sort Date column in a DataTable, is using the required sort plugin. Since the date format in my case was dd/mm/yyyy hh:mm:ss
, I ended up using the date-euro plugin.
All one needs to do is:
Step 1: Include the sorting plugin JavaScript file or code and;
Step 2: Add columnDefs
as shown below to target appropriate column(s).
$('#example').dataTable( {
columnDefs: [
{ type: 'date-euro', targets: 0 }
]
});
Convert the date to the format YYYYMMDD and prepend to the actual value (MM/DD/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.
HTML
<table id="data-table">
<tr>
<td><span>YYYYMMDD</span>MM/DD/YYYY</td>
</tr>
</table>
CSS
#data-table span {
display:none;
}
Specify the column's type
as type: 'date'
:
{title: 'Expiration Date', data: 'ExpirationDate', type: 'date'}
Create a hidden column "dateOrder" (for example) with the date as string with the format "yyyyMMddHHmmss" and use the property "orderData" to point to that column.
var myTable = $("#myTable").dataTable({
columns: [
{ data: "id" },
{ data: "date", "orderData": 4 },
{ data: "name" },
{ data: "total" },
{ data: "dateOrder", visible: false }
] });
You should make use of the HTML5 Data Attributes. https://www.datatables.net/examples/advanced_init/html5-data-attributes.html
Just add the data-order element to your td element.
No plugins required.
<table class="table" id="exampleTable">
<thead>
<tr>
<th>Firstname</th>
<th>Sign Up Date</th>
</tr>
</thead>
<tbody>
<tr>
<td>Peter</td>
<td data-order="2015-11-13 12:00">13. November 2015</td>
</tr>
<tr>
<td>Daniel</td>
<td data-order="2015-08-06 13:44">06. August 2015</td>
</tr>
<tr>
<td>Michael</td>
<td data-order="2015-10-14 16:12">14. October 2015</td>
</tr>
</tbody>
</table>
<script>
$(document).ready(function() {
$('#exampleTable').DataTable();
});
</script>
About update#1, there are 2 problems :
here is the solution to avoid these problems :
jQuery.fn.dataTableExt.oSort['uk_date-asc'] = function (a, b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');
//Date empty
if (ukDatea[0] == "" || ukDateb[0] == "") return 1;
//need to change Date (d/MM/YYYY) into Date (dd/MM/YYYY)
if(ukDatea[0]<10) ukDatea[0] = "0" + ukDatea[0];
if(ukDateb[0]<10) ukDateb[0] = "0" + ukDateb[0];
var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};
//Sorting by Date
jQuery.fn.dataTableExt.oSort['uk_date-desc'] = function (a, b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');
//Date empty
if (ukDatea[0] == "" || ukDateb[0] == "") return 1;
//MANDATORY to change Date (d/MM/YYYY) into Date (dd/MM/YYYY)
if(ukDatea[0]<10) ukDatea[0] = "0" + ukDatea[0];
if(ukDateb[0]<10) ukDateb[0] = "0" + ukDateb[0];
var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};
Just in case someone is having trouble where they have blank spaces either in the date values or in cells, you will have to handle those bits. Sometimes an empty space is not handled by trim function coming from html it's like "$nbsp;". If you don't handle these, your sorting will not work properly and will break where ever there is a blank space.
I got this bit of code from jquery extensions here too and changed it a little bit to suit my requirement. You should do the same:) cheers!
function trim(str) {
str = str.replace(/^\s+/, '');
for (var i = str.length - 1; i >= 0; i--) {
if (/\S/.test(str.charAt(i))) {
str = str.substring(0, i + 1);
break;
}
}
return str;
}
jQuery.fn.dataTableExt.oSort['uk-date-time-asc'] = function(a, b) {
if (trim(a) != '' && a!=" ") {
if (a.indexOf(' ') == -1) {
var frDatea = trim(a).split(' ');
var frDatea2 = frDatea[0].split('/');
var x = (frDatea2[2] + frDatea2[1] + frDatea2[0]) * 1;
}
else {
var frDatea = trim(a).split(' ');
var frTimea = frDatea[1].split(':');
var frDatea2 = frDatea[0].split('/');
var x = (frDatea2[2] + frDatea2[1] + frDatea2[0] + frTimea[0] + frTimea[1] + frTimea[2]) * 1;
}
} else {
var x = 10000000; // = l'an 1000 ...
}
if (trim(b) != '' && b!=" ") {
if (b.indexOf(' ') == -1) {
var frDateb = trim(b).split(' ');
frDateb = frDateb[0].split('/');
var y = (frDateb[2] + frDateb[1] + frDateb[0]) * 1;
}
else {
var frDateb = trim(b).split(' ');
var frTimeb = frDateb[1].split(':');
frDateb = frDateb[0].split('/');
var y = (frDateb[2] + frDateb[1] + frDateb[0] + frTimeb[0] + frTimeb[1] + frTimeb[2]) * 1;
}
} else {
var y = 10000000;
}
var z = ((x < y) ? -1 : ((x > y) ? 1 : 0));
return z;
};
jQuery.fn.dataTableExt.oSort['uk-date-time-desc'] = function(a, b) {
if (trim(a) != '' && a!=" ") {
if (a.indexOf(' ') == -1) {
var frDatea = trim(a).split(' ');
var frDatea2 = frDatea[0].split('/');
var x = (frDatea2[2] + frDatea2[1] + frDatea2[0]) * 1;
}
else {
var frDatea = trim(a).split(' ');
var frTimea = frDatea[1].split(':');
var frDatea2 = frDatea[0].split('/');
var x = (frDatea2[2] + frDatea2[1] + frDatea2[0] + frTimea[0] + frTimea[1] + frTimea[2]) * 1;
}
} else {
var x = 10000000;
}
if (trim(b) != '' && b!=" ") {
if (b.indexOf(' ') == -1) {
var frDateb = trim(b).split(' ');
frDateb = frDateb[0].split('/');
var y = (frDateb[2] + frDateb[1] + frDateb[0]) * 1;
}
else {
var frDateb = trim(b).split(' ');
var frTimeb = frDateb[1].split(':');
frDateb = frDateb[0].split('/');
var y = (frDateb[2] + frDateb[1] + frDateb[0] + frTimeb[0] + frTimeb[1] + frTimeb[2]) * 1;
}
} else {
var y = 10000000;
}
var z = ((x < y) ? 1 : ((x > y) ? -1 : 0));
return z;
};
I Have 10 Columns in my table and there is 2 columns of dates, 2nd column and 4th column is of US Date, so this is worked for me fine. Just paste this code at last in your script section in same sequence.
jQuery.fn.dataTableExt.oSort['us_date-asc'] = function (a, b) {
var x = new Date(a),
y = new Date(b);
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};
jQuery.fn.dataTableExt.oSort['us_date-desc'] = function (a, b) {
var x = new Date(a),
y = new Date(b);
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};
$('#tblPoSetupGrid').dataTable({
columnDefs: [
{ type: 'us_date', targets: 3 },
{ type: 'us_date', targets: 1 }
]
});
I realize this is a two year old question, but I still found it useful. I ended up using the sample code provided by Fudgey but with a minor mod. Saved me some time, thanks!
jQuery.fn.dataTableExt.oSort['us_date-asc'] = function(a,b) {
var x = new Date($(a).text()),
y = new Date($(b).text());
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};
jQuery.fn.dataTableExt.oSort['us_date-desc'] = function(a,b) {
var x = new Date($(a).text()),
y = new Date($(b).text());
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};
Source: Stackoverflow.com