Date sorting problem with jQuery Tablesorter

Question

I am trying to sort a table which has column like 2009-12-17 23:59:59.0. I am using below to apply sort

$(document).ready(function() { 
    $("#dataTable").tablesorter();  
});

But its not working for the dates of format yyyy-mm-dd. Can any one suggest how can i apply this format for sorting?

Ardeth

Define "not working" - how should it work and how does it not? – Walterwalters

Its working fine for AlphaNumeric values and not working for dates of format "yyyy-mm-dd" – Ardeth

Answer 1

The right thing to do would be to add your own parser for this custom format.

Check this to get a grasp on how that works.

jQuery Tablesorter: Add your own parser

Then take a look into the tablesorter source (search for uslongdate, shortdate and then watch how the parsers for date formats are internally done by tablesorter. Now construct your self a similar parser for your particular date format.

jquery.tablesorter.js

This should work to your liking

ts.addParser({
    id: "customDate",
    is: function(s) {
        //return false;
        //use the above line if you don't want table sorter to auto detected this parser
        //else use the below line.
        //attention: doesn't check for invalid stuff
        //2009-77-77 77:77:77.0 would also be matched
        //if that doesn't suit you alter the regex to be more restrictive
        return /\d{1,4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}\.\d+/.test(s);
    },
    format: function(s) {
        s = s.replace(/\-/g," ");
        s = s.replace(/:/g," ");
        s = s.replace(/\./g," ");
        s = s.split(" ");
        return $.tablesorter.formatFloat(new Date(s[0], s[1]-1, s[2], s[3], s[4], s[5]).getTime()+parseInt(s[6]));
    },
    type: "numeric"
});

Now just apply it to the column where you have this format (e.g. assuming the column your custom dates reside in are in column No. 7. (6 means column 7, because the array of the columns is zerobased)

$(function() {
    $("table").tablesorter({
        headers: {
            6: { sorter:'customDate' }
        }
    });
});

Slantwise

Well if I have retrieved Table dynamically and I don't know which Column has the Date format, in that case any Idea? – Ardeth

Check modified code. Now you can leave away the lower code snippet as tablesorter should automagically pick up your sorter for the right columns – Slantwise

@Slantwise , great answer thank you. I'm trying to figure out why the ts doesn't find a match to dates in this format: dd/MM/yyyy . Would appreicate any suggestions. Thanks , Omer – Checkerbloom

brilliant - formats dates in MSSql's default datetime2(7) format. – Simeon

Answer 2

Sort UK/European date format dd/mm/yyyy by:

$("#tableName").tablesorter({dateFormat: "uk"});

Sophistication

Nice- cheers! They missed this one out in the docs! tablesorter.com/docs/#Configuration – Swop

Answer 3

With the latest version 2.18.4 you can simply do like this.Just give the default date format and in the particular column set the column date format this will work ONLY with 'shortDate' sorter.

$('YourTable').tablesorter(
            {
                dateFormat:'mmddYYYY',
                headers: {
                    0: { sorter: false },
                    1: { sorter: true},
                    2: { sorter: true },
                    3: { sorter: true },
                    4: { sorter: "shortDate", dateFormat: "ddmmyyyy" },
                    5: { sorter: true },
                    6: { sorter: false },
                    7: { sorter: false },
                    8: { sorter: false },
                    9: { sorter: false },
                    10: { sorter: false },
                    11: { sorter: false }

                }
            });

Adriel

Answer 4

If you are using date/time format like mm/dd/yyyy hh:mm then use below

$.tablesorter.addParser({ 
        id: "customDate",
        is: function(s) {
            //return false;
            //use the above line if you don't want table sorter to auto detected this parser                        
            //21/04/2010 03:54 is the used date/time format 
            return /\d{1,2}\/\d{1,2}\/\d{1,4} \d{1,2}:\d{1,2}/.test(s);
        },
        format: function(s) {
            s = s.replace(/\-/g," ");
            s = s.replace(/:/g," ");
            s = s.replace(/\./g," ");
            s = s.replace(/\//g," ");
            s = s.split(" ");                       
            return $.tablesorter.formatFloat(new Date(s[2], s[1]-1, s[0], s[3], s[4]).getTime());                                         
        },
        type: "numeric"} );

Actuary

Answer 5

No need to create new parser just use the exisitng one with little modification.

1. Open Jquery plugin js, where you will see the below script.Now just change the date format(desired) for the last else if in your case it is "yy-mm-dd".

    ts.addParser({
    id: "shortDate",
    is: function (s) {
        return /\d{1,2}[\/\-]\d{1,2}[\/\-]\d{2,4}/.test(s);
    }, format: function (s, table) {
        var c = table.config;
        s = s.replace(/\-/g, "/");
        if (c.dateFormat == "us") {
            // reformat the string in ISO format
            s = s.replace(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})/, "$3/$1/$2");
        } else if (c.dateFormat == "uk") {
            // reformat the string in ISO format
            s = s.replace(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})/, "$3/$2/$1");
        } else if (c.dateFormat == "yy-mm-dd" || c.dateFormat == "dd-mm-yy") {
            s = s.replace(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{2})/, "$1/$2/$3");
        }
        return $.tablesorter.formatFloat(new Date(s).getTime());
    }, type: "numeric"
});

2. Now follow the last step mentioned by jitter, but with little modification.

$(function() {
$("table").tablesorter({
    headers: {
        6: { sorter:'shortDate' }
    }
});
});

Aleece

It's rarely a good idea to "hack the core"--it gets overwritten when you or another developer updates the library. – Keller

Answer 6

dateFormat : "mmddyyyy", // set the default date format

example-option-date-format

Allegorical

Answer 7

With TableSorter 2 you can simply use the data-text attribute to store the alternative sort value. I'm doing so in PHP like this to get time considered when sorting, but only show date in the table:

<td data-text="<?php xecho(date_format($date, "Y-m-d H:i:s")); ?>">
    <?php xecho(date_format($date, "F d, Y")); ?>
</td>

No formatting or parsing necessary.

Keller

Answer 8

Just add another choice works perfectly for me to sort the date format (dd/MM/yyyy hh:mm:ss). By using the js dataTables plugin.

Add the code below to your is code:

$(document).ready(function () {
oTable = $('#AjaxGrid').dataTable({
"aLengthMenu": [[5, 10, 25, 50, 100, 500,1000,-1], [5, 10, 25, 50, 100,500,1000,"All"]],
iDisplayLength: 1000,
aaSorting: [[2, 'asc']],
bSortable: true,
aoColumnDefs: [
{ "aTargets": [ 1 ], "bSortable": true },
{ "aTargets": [ 2 ], "bSortable": true },
{ "aTargets": [ 3 ], "bSortable": true },
{ "aTargets": [ 4 ], "bSortable": true },
{"aTargets": [ 5 ], "bSortable": true, "sType": "date-euro"},
{"aTargets": [ 6 ], "bSortable": true, "sType": "date-euro"},
{ "aTargets": [ 7 ], "bSortable": false }
],
"sDom": '<"H"Cfr>t<"F"ip>',
"oLanguage": {
"sZeroRecords": "- No Articles To Display -",
"sLengthMenu": "Display _MENU_ records per page",
"sInfo": " ", //"Displaying _START_ to _END_ of _TOTAL_ records",
"sInfoEmpty": " ", //"Showing 0 to 0 of 0 records",
"sInfoFiltered": "(filtered from _MAX_ total records)"
},
"bJQueryUI": true
});
});


//New code
jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-euro-pre": function ( a ) {
if ($.trim(a) != '') {
var frDatea = $.trim(a).split(' ');
var frTimea = frDatea[1].split(':');
var frDatea2 = frDatea[0].split('/');
var x = (frDatea2[2] + frDatea2[1] + frDatea2[0] + frTimea[0] + frTimea[1] + frTimea[2]) * 1;
} else {
var x = 10000000000000; // = l'an 1000 ...
}

return x;
},

"date-euro-asc": function ( a, b ) {
return a - b;
},

"date-euro-desc": function ( a, b ) {
return b - a;
}
} );

Starstarboard