Sorting date in datatable

Question

I'm trying to sort dates in my datatable like DD/MM/YYYY (day, month, year) . I was following https://datatables.net/plug-ins/sorting/ .

but all the date sorts seem to be deprecated and point to the datetime plugin: https://datatables.net/blog/2014-12-18

I don't seem to be able to get the datetime plugin working to sort. I tried the old way, with date. The initialize looks like this:

var historiektable = $('#dataTableHistoriek').DataTable({
    "paging" : false,
    "ordering" : true,
    "scrollCollapse" : true,
    "searching" : false,
    "columnDefs" : [{"targets":3, "type":"date"}],
    "bInfo": true
});

Without sorting it shows the table results like this:

When I put ordering:true 2 of the 2016 dates appear somewhere else in the list (so, not in order you would expect)

With everything pointing at Moment I thought I needed to sort with that. But I'm not sure how.

I saw $.fn.dataTable.moment('DD.MM.YYYY'); somewhere, but I understood that the fn doesn't work with this newest version of datatables anymore?

Anyone knows how to sort dates?

Vedic

Was to late, you can very easily do it yourself -> jsfiddle.net/mwhpy9eb more saitsifying than a plugin you are not sure how works or is having exaggerated dependencies. – Extortioner

I tried that too, but it didn't work at first. – Vedic

Please look into this answer for an alternate way to sort data by date. – Desolation

Answer 1

Use date-eu sorting plugin to sort dates in the format DD/MM/YY.

Include the following JS file //cdn.datatables.net/plug-ins/1.10.11/sorting/date-eu.js and use the code below:

var historiektable = $('#dataTableHistoriek').DataTable({
    "paging" : false,
    "ordering" : true,
    "scrollCollapse" : true,
    "searching" : false,
    "columnDefs" : [{"targets":3, "type":"date-eu"}],
    "bInfo": true
});

Mom

Thanks a lot. I was considering it earlier but I was hoping for a more universal solution. I guess there is no since Ultimate date sorting did not work for me and this did. – Camelliacamelopard

Answer 2

The example of Gyrocode.com seems the most effective. A recent solution says to use Moments.js but it does not work in my case. date-eu is deprecated by DataTables but it works perfectly.

If you want to sort by date and time using the date format dd/MM/yyyy HH:mm, use date-euro in the same way.

var table = $('#example-table').DataTable({
    columnDefs: [{ 'targets': 0, type: 'date-euro' }],
    order: [0, 'desc'],
});

For beginners, add the JS file date-euro to your site. Then add "columnDefs" in your code to indicate which column date-euro should be applied: targets = indicates the column containing the dates to sort, type = indicates the date-euro function to apply to the column. Finally, add "order" to apply the sort you want.

Orthogenic

you are a life saver bro, 2019 and you did it. – Ceramist

Answer 3

For me, using ASP.NET core 3.1 with MVC, I used a data-sort attribute on my <td> for the datatables:

                    <td data-sort="@(item.DueDateTime.Ticks)">
                        @Html.DisplayFor(modelItem => item.DueDateTime)
                    </td>

No plug-ins needed

See this link: https://datatables.net/examples/advanced_init/html5-data-attributes.html

Pervious

Answer 4

Please look into this answer for an alternate way to sort data by date.

Sample code::

<td data-search="21st November 2016 21/11/2016" data-order="1479686400">
    21st November 2016
</td>

$('#dataTable').DataTable({
    "order": [[10, 'desc']],
});

Thank You,

Happy Coding :)

Desolation

Answer 5

Please refer to this pen: https://codepen.io/arnulfolg/pen/MebVgx

It uses //cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js and //cdn.datatables.net/plug-ins/1.10.12/sorting/datetime-moment.js for sorting datatable

To sort the table by default use:

$.fn.dataTable.moment('DD/MM/YY');
$('#example').DataTable({ 
       "order": [[ 3, "desc" ]] 
    }); 

Germanium

Answer 6

Following Plasebo's example works, but in my case the MySQL DATE_FORMAT was sorting on month value, not entire date. My solution was to remove the DATE_FORMAT from my SQL statement.

$(document).ready(function() {
  $.fn.dataTable.moment('DD/MM/YY');
  $('.happyTable').DataTable({
        "ordering": true,
        "order": [[ 1, "desc" ]],
  });
});

DATE_FORMAT(date,'%m/%d/%Y')

"2003-12-30 00:00:00" results in "12/30/2003" but sorts on month value.

Allowedly

Answer 7

You can do your own comparator in order to keep the control of how is ordering the dates.

 jQuery.extend(jQuery.fn.dataTableExt.oSort, {
            "ddMmYyyy-pre": function (a) {
                a = a.split('/');
                if (a.length < 2) return 0;
                return Date.parse(a[0] + '/' + a[1] + '/' + a[2]);
            },
            "ddMmYyyy-asc": function (a, b) {
                return ((a < b) ? -1 : ((a > b) ? 1 : 0));
            },
            "ddMmYyyy-desc": function (a, b) {
                return ((a < b) ? 1 : ((a > b) ? -1 : 0));
            }
        });

As you can see in the above comparator you can choose how to parse the date depending on your data.

And in the columns definition:

  "columnDefs": [
            {
                targets: [4], type: "ddMmYyyy"
            }]

Morna

Answer 8

There's an ugly hack that I've used in the past especially when I couldn't quickly modify the DataTable javascript. You can add a hidden span that has the date in a sortable format.

<td><span style="visibility:hidden">2006-12-21</span>21/12/2006</td>

Beatify

Answer 9

test strong text

$.fn.dataTableExt.oSort['time-date-sort-pre'] = function(value) {      
    return Date.parse(value);
};
$.fn.dataTableExt.oSort['time-date-sort-asc'] = function(a,b) {      
    return a-b;
};
$.fn.dataTableExt.oSort['time-date-sort-desc'] = function(a,b) {
    return b-a;
};

var table = $('#example').DataTable({
    columnDefs : [
        { type: 'time-date-sort', 
          targets: [0],
        }
    ],
    order: [[ 0, "desc" ]]
});

Isobath

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – San

Answer 10

This proved WAY trickier than it needed to be. I tried moment, luxon , as well as all the answers above and none was working for my dates (DD/MM/YYYY) format.

It turned out it was as simple as converting my date to iso8601 format and inserting it into the data-order attribute. No other libraries were needed.

< td data-order="1479686400">
30/01/2024
 </td>

I'm using Asp.net Core but this should work regardless of your framework.

Depot