Datatables - Can't sort by column different than string type

Question

I have a datatable with 'date' column of type String. Now, when I want to sort by it, it's obviously not sorting properly for obvious reasons, so I changed the sType to 'date' and everything is fine.
The problem however is when I change the sType from string to something else, I cannot sort by this column anymore. Initial sort after page load is sorted and fine, but I can't click the column anymore to sort by it. It behaves like bSortable is false, but it isn't (debugged it). bSort is also true.
Moreover, I've tried implementing custom comparators, including sort plugins etc. same results.
Any ideas what might be causing the problem?

EDIT:
My datatable init process:

$('#scanDataTable').dataTable({
     "bJQueryUI": true,
     "sPaginationType": "full_numbers",
     "sDom": '<""l>t<"F"fp>',
     "aaSorting": [[0, "asc"]],
     "iDisplayLength": "100",
     "aoColumnDefs": [
          {"sType": "date", "aTargets": [0]}
     ]
});

If the "sType" parameter is string, clicking on the column header switches between asc and desc sorting (but it's string so the sorting is incorrect). After switching "sType" to date or anything else than string, clicking on table header does nothing.
As I mentioned earlier, I tried with "bSortable": true in column 0 options and "bSort": true in dataTable options, and imported custom sorting plugins, but it doesn't change anything. My date format is: 17.11.2014 21:54:39.

Important: After messing a bit with aaSorting, I noticed that both asc and desc sorting is the same which probably means that the sorting itself is the problem, and not the table options.

"Any ideas what might be causing the problem?" It is impossible to say when we dont have any code to look at :) Please add your script and example of markup to the question. — davidkonrad
– davidkonrad, Commented Apr 13, 2015 at 23:33
I didn't include any code because there is really nothing special to show. It's just a regular simplest datatable but fair enough. I'll add some code when I get home. — Krystian Sikora
– Krystian Sikora, Commented Apr 14, 2015 at 4:49

davidkonrad · Accepted Answer · 2015-04-14 16:27:24Z

2

Your date format is not compatible with new Date() or Data.parse(), thats why the column sorting becomes "frozen" when you try to apply the default date type to the column. It is trying to sort a bunch of NaN strings. So you must create an appropriate plugin yourself.

Here is an example, a sikor-date that parses your format dd.mm.yyyy hh:mm:ss to standard UTC time :

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
    "sikor-date-pre": function(a) {
        var dateParts = a.split('.'),
        time = a.split(' ')[1].split(':'),
        year = parseInt(dateParts[2])-1900,
        month = parseInt(dateParts[1])-1,
        day = parseInt(dateParts[0]),
        hours = parseInt(time[0]),
        mins = parseInt(time[1]),
        secs = parseInt(time[2]);
        return Date.UTC(year, month, day, hours, mins, secs);
    },
    "sikor-date-asc": function(a, b) {
        return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },
    "sikor-date-desc": function(a,b) {
        return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    }
});

The entire date string is splitted into logical parts, parsed to integers and then converted to a compareable number with Date.UTC().

demo -> http://jsfiddle.net/nm261eyn/

This sorting plugin should work with all versions of dataTables. Use it as

"aoColumnDefs": [
    {"sType": "sikor-date", "aTargets": [0]}
]

TODO : Errorhandling, check if dates meet the requirements, check if it is an empty string "" etc. The plugin assumes blindly that dates comes as a string in the exact format dd.mm.yyyy hh:mm:ss.

Test table :

<tbody>
    <tr><td>17.11.2014 21:54:39</td></tr>
    <tr><td>18.11.2013 21:54:39</td></tr>
    <tr><td>17.11.2014 1:54:39</td></tr>        
    <tr><td>17.11.2014 1:54:38</td></tr>                
    <tr><td>16.11.2014 22:54:39</td></tr>        
    <tr><td>16.11.2015 00:00:00</td></tr>                
</tbody>

edited Apr 14, 2015 at 16:27

answered Apr 14, 2015 at 13:54

davidkonrad

85.7k17 gold badges211 silver badges273 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Krystian Sikora Over a year ago

I thought that this format might not be compatible so I downloaded a plugin which theoretically supports multiple new date formats, but after setting sType to that plugin type, nothing changed. After seing your solution, my guess is that it didn't support the date format I'm using either. Thank you for your time, this solved my problem. Cheers.

Krystian Sikora Over a year ago

Also changed dateParts = a.split('.') to dateParts = a.split(' ')[0].split('.') and removed -1900 in year variable. Could you please explain what was with this -1900? Thanks in advance.

davidkonrad Over a year ago

Hey @Sikor, you are welcome, thank you for accepting the answer! Click on the link above "Date.UTC()" - year is expected to be "A year after 1900". It is just to calculate the correct i.e the same date as integer value. The sorting will of course work either way, since there is no difference in years between say 3913 and 3914 compared to 2013 and 2014..

davidkonrad Over a year ago

month = parseInt(dateParts[1])-1 though, uodated the answer. Sry for that blunder. Knew it but forgot it.

Krystian Sikora Over a year ago

Thanks, I get it now but isn't it supposed to be +1900 instead of -1900? It's also a bit strange for me that this function uses 0-11 for months but 1-31 for days (instead of 0-30 for example).

|

Collectives™ on Stack Overflow

Datatables - Can't sort by column different than string type

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related