DataTables custom date sort function

Question

I know about http://www.datatables.net/plug-ins/sorting please do not post about it as it will not assist me.

I am looking to create a custom date sorting system for use with our CMS. The issue is that the customer can choose what their date format is and it is preventing proper sorting. I have just started working with dataTables and this is my first attempt at a custom sorting system. The code below gathers the correct information on click but does not resort the field.

$.fn.dataTable.ext.order['date-custom']=function(settings,col){return this.api().column(col,{order:'index'}).nodes().map(function(td,i){

    function splitDate(str){
        if(str.indexOf("-") >= 0){ str=str.split('-'); }
        else if(str.indexOf("/") >= 0){ str=str.split('/'); }
        else if(str.indexOf(".") >= 0){ str=str.split('.'); }
        else{str='';}
        return str;
    }

    // Needs to pull attribute  "data-format" from the table            
    if($(td).parent().parent().parent().attr('data-date-format') !== undefined  && $(td).parent().parent().parent().attr('data-date-format')!=''){ 

        var format= $(td).parent().parent().parent().attr('data-date-format'); 

        var a = $(td).text(); 
        var p = splitDate(a); 
        var result ='10000000000000';
        if(p.length==3){
            switch(format){
                case 'm-d-Y':result = p[2]+p[0]+p[1]; break;
                case 'm-d-y':result = p[2]+p[0]+p[1]; break;
                case 'm/d/Y':result = p[2]+p[0]+p[1]; break;
                case 'm/d/y':result = p[2]+p[0]+p[1]; break;
                case 'm.d.Y':result = p[2]+p[0]+p[1]; break;
                case 'm.d.y':result = p[2]+p[0]+p[1]; break;
                case 'Y-m-d':result = p[0]+p[1]+p[2]; break;
                case 'y-m-d':result = p[0]+p[1]+p[2]; break;
                case 'Y/m/d':result = p[0]+p[1]+p[2]; break;
                case 'y/m/d':result = p[0]+p[1]+p[2]; break;
                case 'Y.m.d':result = p[0]+p[1]+p[2]; break;
                case 'y.m.d':result = p[0]+p[1]+p[2]; break;
                case 'd-m-Y':result = p[2]+p[1]+p[0]; break;
                case 'd-m-y':result = p[2]+p[1]+p[0]; break;
                case 'd/m/Y':result = p[2]+p[1]+p[0]; break;
                case 'd/m/y':result = p[2]+p[1]+p[0]; break;
                case 'd.m.Y':result = p[2]+p[1]+p[0]; break;
                case 'd.m.y':result = p[2]+p[1]+p[0]; break;
            }
        } 
    }else{  var result='10000000000000';}
    return result; 
});}

Table Code

$("#table").dataTable({
    columns:[ null,null,null,{ "orderDataType": "date-custom" }];
}

Gyrocode.com · Accepted Answer

SOLUTION #1

You should add -pre to the name of your custom sorting function, see Ordering plug-in development - Pre-deformatting for more information.
```
$.extend($.fn.dataTableExt.oSort, {
    "date-custom-pre":function(a){
        // ... skipped ...
    }
});
```

There were issues with your splitDate function. The correct code is

function splitDate(str){
    if(str.indexOf("-") >= 0){ str = str.split('-'); }
    else if(str.indexOf("/") >= 0){ str = str.split('/'); }
    else if(str.indexOf(".") >= 0){ str = str.split('.'); }
    else{ str='';}
    return str;
}

You won't be able to access table with $(this).parent().parent() as this refers to window Object. I hard-coded the date format until workaround is available.

DEMO

See this jsFiddle for code and demonstration.

SOLUTION #2

Use Custom data source ordering to get access to the table inside the sorting function.

$.fn.dataTable.ext.order['date-custom'] = function  ( settings, col )
{ 
    var api = this.api();

    return api.column( col, {order:'index'} ).nodes().map( function ( td, i ) {
        function splitDate(str){
            if(str.indexOf("-") >= 0){ str = str.split('-'); }
            else if(str.indexOf("/") >= 0){ str = str.split('/'); }
            else if(str.indexOf(".") >= 0){ str = str.split('.'); }
            else{ str='';}
            return str;
        }

        var format = $(api.table().node()).attr('data-format');                 
        var a = $(td).text();

        // Collect date from field
        var p = splitDate(a);
        var result ='10000000000000';
        if(p.length==3){
            switch(format){
                case 'm-d-Y':result = p[2]+p[0]+p[1]; break;
                case 'm-d-y':result = p[2]+p[0]+p[1]; break;
                case 'm/d/Y':result = p[2]+p[0]+p[1]; break;
                case 'm/d/y':result = p[2]+p[0]+p[1]; break;
                case 'm.d.Y':result = p[2]+p[0]+p[1]; break;
                case 'm.d.y':result = p[2]+p[0]+p[1]; break;
                case 'Y-m-d':result = p[0]+p[1]+p[2]; break;
                case 'y-m-d':result = p[0]+p[1]+p[2]; break;
                case 'Y/m/d':result = p[0]+p[1]+p[2]; break;
                case 'y/m/d':result = p[0]+p[1]+p[2]; break;
                case 'Y.m.d':result = p[0]+p[1]+p[2]; break;
                case 'y.m.d':result = p[0]+p[1]+p[2]; break;
                case 'd-m-Y':result = p[2]+p[1]+p[0]; break;
                case 'd-m-y':result = p[2]+p[1]+p[0]; break;
                case 'd/m/Y':result = p[2]+p[1]+p[0]; break;
                case 'd/m/y':result = p[2]+p[1]+p[0]; break;
                case 'd.m.Y':result = p[2]+p[1]+p[0]; break;
                case 'd.m.y':result = p[2]+p[1]+p[0]; break;
            }
        }

        return result;
    } );
};

$(document).ready(function (){
    var table = $('#example').DataTable({
       columnDefs: [
           { targets: 4, orderDataType: 'date-custom', type: 'string' }
       ]
    });
});

Please note that you need to add column definition with columns or columnDefs and use the following options orderDataType: 'date-custom', type: 'string'.

DEMO

See this jsFiddle for code and demonstration.

DataTables custom date sort function

Answers (1)

Related Questions