datatables: sorting a date-range column

Question

I have a datatable with aoColumnDefs containing:

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

The dates in this column are strings in the form "MM/dd/yyyy - MM/dd/yyyy". Unfortunately, this column is currently sorting incorrectly. Instead of sorting by year, then month, then day, it is sorting by month then day then year.

I assume that this is a date format issue (because it is sorting the date parts from left to right, assuming that my date is yyyy/mm/dd).

Is there a way to set the date format used while sorting dates on datatables?

NOTE: I know I can sort on a hidden field. This was causing me other issues, and I want to know if I can solve the problem another way.

BLSully BLSully · Accepted Answer · 2012-09-25T20:15:41

Start here: http://datatables.net/development/sorting#type_based

To add a new sort function to DataTables you need to attach your function to the object $.fn.dataTableExt.oSort. For example, the following adds a case sensitive sorting function of type 'string-case':

jQuery.fn.dataTableExt.oSort['date-range-asc']  = function(x,y) {
    //x = "MM/dd/yyyy - MM/dd/yyyy";
    var regex = /(\d{2}\/\d{2}\/\d{4})/,
        matchesX = regex.exec(x),
        matchesY = regex.exec(y);        

    //for example, only sort on first date..
    x = Date.parse(matchesX[0]);
    y = Date.parse(matchesY[0]);

    return ((x < y) ? -1 : ((x > y) ?  1 : 0));
};

The code above is probably broken...but it should get you on the right track

mtmurdock's solution

Add this code to site.js (or anywhere imported after datatables):

jQuery.extend(jQuery.fn.dataTableExt.oSort, {
    "my-date-range-pre": function (a) {
        var ukDatea = a.split('/');

        var year = $.trim(ukDatea[2].split('-')[0]);
        var month = $.trim(ukDatea[0]);
        var day = $.trim(ukDatea[1]);

        if (month.length == 1)
            month = "0" + month;
        if (day.length == 1)
            day = "0" + day;

        return (year + month + day) * 1;
    },

    "my-date-range-asc": function (a, b) {
        var result = ((a < b) ? -1 : ((a > b) ? 1 : 0));
        return result;
    },

    "my-date-range-desc": function (a, b) {
        var result = ((a < b) ? 1 : ((a > b) ? -1 : 0));
        return result;
    }
});

Then on your table definition set the aoColumnDefs like this:

{ "sType": "my-date-range", "aTargets": [0] }

datatables: sorting a date-range column

1 Answers