jquerysortingdatatablesdate-sorting

How to sort by date correctly with jQuery Plugin Data Tables


I'd like to know how I can sort my tables by a date correctly. I'm using jquery with the plugin Data Tables (http://datatables.net/).

I also found a useful plugin on this site for my problem, which I integrated into my code. My JS-Code looks like this:

<script>
$(document).ready(function() {

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));
};

   var oTable = $('#example').dataTable( {
        "bJQueryUI": true,
        "sPaginationType": "full_numbers",
        "aoColumns": [
            null,
            { "sType": "uk_date" },
            null,
            null,
            null,
            null,
            null
        ]
    } );


$(".search_init").click(function(){
    var input_value = $(this).val();
    $(this).val("");
});     

$("tfoot input").keyup( function () {
    oTable.fnFilter( this.value, $("tfoot input").index(this) );
} );

});
</script>

My table looks like this:

<table id="example">
<thead>
    <tr>
        <th>Some Text</th>
        <th>Date</th>
        <th>Some Text</th>
        <th>Some Text</th>
        <th>Some Text</th>
        <th>Some Text</th>
        <th>Some Text</th>
    </tr>
</thead>
<tbody>
    <tr>
        <td>Some text</td>
        <td>22/07/2011</td>
        <td>Some text</td>
        <td>Some text</td>
        <td>Some text</td>
        <td>Some text</td>
        <td>Some text</td>
    </tr>
    ...
</tbody>
<tfoot>
    <tr>
        <td><input type="text" class="search_init" value="Text" /></td>
        <td><input type="text" class="search_init" value="Text" /></td>
        <td><input type="text" class="search_init" value="Text" /></td>
        <td><input type="text" class="search_init" value="Text" /></td>
        <td><input type="text" class="search_init" value="Text" /></td>
        <td><input type="text" class="search_init" value="Text" /></td>
        <td><input type="text" class="search_init" value="Text" /></td>
    </tr>
</tfoot>
</table>

Could someone explain me why this isn't working? I'm able to sort each column except the one with the dates in it.

I include following libraries:

<script src="[PATH]/js/jquery-1.6.2.min.js" language="JavaScript" type="text/javascript"></script>
<script src="[PATH]/js/jquery-ui-1.8.16.custom.min.js" type="text/javascript"></script>
<script src="[PATH]/js/jquery.dataTables.min.js" language="JavaScript" type="text/javascript"></script>
<script src="[PATH]/js/ZeroClipboard.js" type="text/javascript" charset="utf-8"></script>
<script src="[PATH]/js/TableTools.js" type="text/javascript" charset="utf-8"></script>

Solution

  • If you are interested in trying another plugin, here is a solution for the popular Tablesorter plugin that I've used in the past. Basically just use the plugin and create a custom parser for your dates.

    Here is the date parser I created:

    /*
    * Parser Type: DateTime
    * Parser Format: "ddd, MMM d, yyyy h:mm tt"
    */
    $.tablesorter.addParser({
        // set a unique id
        id: 'LongDateTimeFormat',
        is: function (s) {
            // return false so this parser is not auto detected 
            return false;
        },
        format: function (s) {
            s = s.trim();
    
            // format your data for normalization
            s = s.match(/(?!^[A-Za-z]{3}, )[A-Za-z]{3} [0-9]{1,2}, [0-9]{4} [0-9]{1,2}:[0-9]{2}/) + ":00 " + s.match(/[A-Z]{2}$/);
    
            return new Date(s).getTime();
        },
        // set type, either numeric or text 
        type: 'numeric'
    });
    

    The expected date format is "ddd, MMM d, yyyy h:mm tt" as indicated above. The parser converts the date string to a date object and returns the number of milliseconds since midnight of January 1, 1970 to the specified date.