I'm currently working on datatable, I found my sorting for date column was not working. here was my screenshot
Here was my code
<table id="tbl" class="table table-small-font table-bordered table-striped">
<thead>
<tr>
<th> </th>
<th class="text-left">Dated</th>
<th class="text-left">Day</th>
<th class="text-center">Remarks</th>
<th class="text-center">State</th>
<th class="text-center"></th>
</tr>
</thead>
<tbody>
@{ IEnumerable
<PublicHoliday> PublicHolidays = (IEnumerable
<PublicHoliday>)ViewData["PublicHolidays"]; int Idx = 1; } @foreach (var i in PublicHolidays) {
<tr>
<td>@Idx</td>
<td>@i.Dated.ToString("dd-MMM-yyyy hh:mm tt")</td>
<td>@i.Dated.ToString("ddd")</td>
<td>@Html.DisplayFor(x => i.Remarks)</td>
<td>@i.ForStateName</td>
<td><a data-toggle="tooltip" title="Delete" onclick="DeleteRecord(@i.Id);"><span class="glyphicon glyphicon-remove"></span></a></td>
</tr>
Idx++; }
</tbody>
</table>
<script src="~/lib/jquery-ui-1.12.1/jquery-ui.min.js"></script>
<script src="~/js/jquery.dataTables.min.js"></script>
<script src="~/js/dataTables.bootstrap.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
var tbl = $('#tbl').DataTable({
dom: "<'row'<'col-sm-6 col-xs-7'l><'col-sm-6 col-xs-7'f>>" + "rtip",
order: [
[0, "asc"]
],
pagingType: "numbers",
iDisplayLength: 50
});
});
</script>
the sort column does not work at all, and I can't find any plugin from datatable to use. anyone please help.. thanks in advance
I'm currently working on datatable, I found my sorting for date column was not working. here was my screenshot
Here was my code
<table id="tbl" class="table table-small-font table-bordered table-striped">
<thead>
<tr>
<th> </th>
<th class="text-left">Dated</th>
<th class="text-left">Day</th>
<th class="text-center">Remarks</th>
<th class="text-center">State</th>
<th class="text-center"></th>
</tr>
</thead>
<tbody>
@{ IEnumerable
<PublicHoliday> PublicHolidays = (IEnumerable
<PublicHoliday>)ViewData["PublicHolidays"]; int Idx = 1; } @foreach (var i in PublicHolidays) {
<tr>
<td>@Idx</td>
<td>@i.Dated.ToString("dd-MMM-yyyy hh:mm tt")</td>
<td>@i.Dated.ToString("ddd")</td>
<td>@Html.DisplayFor(x => i.Remarks)</td>
<td>@i.ForStateName</td>
<td><a data-toggle="tooltip" title="Delete" onclick="DeleteRecord(@i.Id);"><span class="glyphicon glyphicon-remove"></span></a></td>
</tr>
Idx++; }
</tbody>
</table>
<script src="~/lib/jquery-ui-1.12.1/jquery-ui.min.js"></script>
<script src="~/js/jquery.dataTables.min.js"></script>
<script src="~/js/dataTables.bootstrap.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
var tbl = $('#tbl').DataTable({
dom: "<'row'<'col-sm-6 col-xs-7'l><'col-sm-6 col-xs-7'f>>" + "rtip",
order: [
[0, "asc"]
],
pagingType: "numbers",
iDisplayLength: 50
});
});
</script>
the sort column does not work at all, and I can't find any plugin from datatable to use. anyone please help.. thanks in advance
Share Improve this question edited Dec 12, 2017 at 8:13 Bhuwan 16.9k5 gold badges37 silver badges60 bronze badges asked Dec 12, 2017 at 4:50 Lee Yuan YeeLee Yuan Yee 531 silver badge7 bronze badges 4- 1 Maybe you can add a hidden column by convert date into timetamp(long), and the sort by it, ref: stackoverflow./a/32669709/9041712 – Yu-Lin Chen Commented Dec 12, 2017 at 4:56
- @Yu-LinChen, I was going to suggest the same, I've done exactly this many times before, its easier to deal with a long. – SPlatten Commented Dec 12, 2017 at 6:48
- May i know how the codes will be looks like? – Lee Yuan Yee Commented Dec 12, 2017 at 7:10
- @LeeYuanYee, I add the sample code below: stackoverflow./a/47775236/9041712 – Yu-Lin Chen Commented Dec 12, 2017 at 14:40
4 Answers
Reset to default 2I am not really sure that there is any plugin that supports date-dd-MMM-yyyy hh:mm tt format
So i have made modification to the plugin to support this format.
Here is the code for it.Load this piece of code after loading the datatable
plugin
(function() {
var customDateDDMMMYYYYToOrd = function(date) {
var dateTime = date.split(' '),
dateObj = new Date(dateTime[0].replace(/-/g, ' ')),
time = "00:00",
type = "AM";
if (dateTime.length > 1) { // if time part and am/pm part is available
time = dateTime[1],
type = dateTime[2];
}
var splitTime = time.split(":"),
hours = type == "PM" ? Number(splitTime[0]) + 12 : Number(splitTime[0]),
minutes = Number(splitTime[1]),
seconds = 0,
milliseconds = 0;
return new Date(dateObj.getFullYear(), dateObj.getMonth(), dateObj.getDate(), hours, minutes, seconds, milliseconds);
};
// This will help DataTables magic detect the "dd-MMM-yyyy" format; Unshift
// so that it's the first data type (so it takes priority over existing)
jQuery.fn.dataTableExt.aTypes.unshift(
function(sData) {
"use strict"; //let's avoid tom-foolery in this function
if (/^([0-2]?\d|3[0-1])-(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)-\d{4}/i.test(sData)) {
return 'date-dd-mmm-yyyy';
}
return null;
}
);
// define the sorts
jQuery.fn.dataTableExt.oSort['date-dd-mmm-yyyy-asc'] = function(a, b) {
"use strict"; //let's avoid tom-foolery in this function
var ordA = customDateDDMMMYYYYToOrd(a),
ordB = customDateDDMMMYYYYToOrd(b);
return (ordA < ordB) ? -1 : ((ordA > ordB) ? 1 : 0);
};
jQuery.fn.dataTableExt.oSort['date-dd-mmm-yyyy-desc'] = function(a, b) {
"use strict"; //let's avoid tom-foolery in this function
var ordA = customDateDDMMMYYYYToOrd(a),
ordB = customDateDDMMMYYYYToOrd(b);
return (ordA < ordB) ? 1 : ((ordA > ordB) ? -1 : 0);
};
})();
The above code is the modification of date sort plugin(dd-mmm-yyyy
).I have modified the customDateDDMMMYYYYToOrd
function to fit this specific example
Add this so that the plugin would know what to be use when date is sorted
var tbl = $('#tbl').DataTable({
dom: "<'row'<'col-sm-6 col-xs-7'l><'col-sm-6 col-xs-7'f>>" + "rtip",
order: [[0, "asc"]],
pagingType: "numbers",
pageLength: 50,
columnDefs: [
{ type: 'date-dd-mmm-yyyy', targets: 1 } //add this part
]
});
You can find the list of sorting plugins available for datatable
here
Note:
Please note that this plug-in is **deprecated*. The datetime plug-in provides enhanced functionality and flexibility
Since 02-Jan-2017 12:00 AM
and so on is valid RFC2822 dates, all you have to do is to set the column type
to date
:
columnDefs: [
{ targets: 1, type: 'date' }
]
Or if you have some odd values in the data, like null
values you can just pass back the parsed value and sort as number, by that forcing correct sorting anyway (I guess that is the real problem) :
columnDefs: [
{ targets: 1,
type: 'num',
render: function(data,type) {
if (type == 'sort') return Date.parse( data ).valueOf()
return data
}
}
]
There is really no need for a special sorting plugin here. A third option is to set the unformatted date as data-sort
attribute on the itself
<td data-sort="@i.Dated.ToString()">@i.Dated.ToString("dd-MMM-yyyy hh:mm tt")</td>
I use momentjs and lodash, it would like:
var records = [
["1", "28-Jan-2017 12:00 AM"],
["1", "28-May-2017 12:00 AM"],
["1", "28-Mar-2017 12:00 AM"]
];
records = _.map(records, record => {
record.push(moment(record[1], "DD-MMM-YYYY hh:mm A").unix());
return record;
});
$(document).ready(function() {
$('#example').DataTable( {
data: records,
columns: [
{ title: "id" },
{ title: "date" },
{ title: "ts"}
],
'columnDefs': [
{ 'orderData':[2] },
{
'targets': [2],
'visible': false
},
],
});
});
$(document).ready(function() {
$('#example').DataTable( {
data: records,
columns: [
{ title: "id" },
{ title: "date" },
{ title: "ts"}
],
'columnDefs': [
{ 'orderData':[2] },
{
'targets': [2],
'visible': false
},
],
});
});
Here is the jsFiddle
You can add the following sorting method to your options to sort according to date and time , specially if your time in 12 hour(AM/PM) format. I use the following code and it works for me:
$('#tableid').DataTable({ "order": [[, 'desc']],//order column in descending order. "columnDefs": [ { "type": "date", "targets": }//date column formatted like "03/23/2018 10:25:13 AM". ], "pageLength": 25 });