Problem with sorting dates with jquery tablesorter

Question

I am using tablesorter plugin to sort my tables in an MVC .NET App. Most of my columns are strings and I'm having no problems with them. Neither with the numeric ones. The thing is my datetime columns are also getting sorted as if they were strings. They get sorted like this: 01/04/2009, 02/02/2009, 03/08/2009, etc. I obtain the data from the Model in that View.

My call is the default one:

$("#table").tablesorter();

I tried specifying dateformat with no luck:

$("#table").tablesorter({
            dateFormat: 'dd/mm/yyyy'});

The odd thing happens when I manually type a static table with random dates. It gets sorted! But my data comes from a DB call and is put into the Model, I then itreate through it and write tr's with the data.

Thanks in advance.

EDIT: Could it be something related with the way I create the tr's?

<% foreach (var item in Model) { %>
<tr>
<td>
<%= Html.Encode(item.date) %>
</td>
<td>...</td>
<td>...</td>
<td>...</td>
</tr>
<% } %>

Egidio

Is it possible to format your date in yyyy-mm-dd? – Oriya

nope, it has to be in my country's locale (Spain). :( – Egidio

Answer 1

Try adding a Tablesorter parser to your date column. Tablesorter comes with a parser for shortDate, usLongDate and isoDate.

$("#table").tablesorter({
    headers: { colNum: { sorter: 'shortDate'} }
});

where colNum is the column with the dates. The only example I could find on the tablesorter site is here. This also works if tablesorter is sorting numbers wrong as well. There are other parsers as well for percent, ip address and more. Take a look near the end of the source code and they'll be listed there.

Edit: In looking at the source code, the dateFormat option appears to be looking only for "us", "uk", "dd/mm/yy" or "dd-mm-yy". What happens when you try "uk"?

Lettuce

I tried shorDate parser aswell but forgot to mention. No luck either :( I'll give a try with the other date parsers and let you know. Thanks – Egidio

That worked! I put the call as follows: $("#table").tablesorter({dateFormat: 'uk'}); I keep wondering why, with the default call, works as intended when writing a table in plain HTML and not when MVC-generating it (and having to find this workaround). Thanks! – Egidio

Once again StackOverflow appears to be the only place with the correct answer! dateFormat: 'uk' works for me too, while dateFormat: "dd/mm/yyyy" doesn't. – Sear

dd/mm/yyyy does not exist ;) – Adenosine

@CasBloem which is lame, because their own documentation claims to support it: mottie.github.io/tablesorter/docs/… – Causal

Using " dateFormat: 'ddmmyyyy' " worked for me. It doesn't understand "dd/mm/yyyy". – Barby

Answer 2

I got the same problem, and I added a custom parser called datetime:

$.tablesorter.addParser({
    id: "datetime",
    is: function(s) {
        return false; 
    },
    format: function(s,table) {
        s = s.replace(/\-/g,"/");
        s = s.replace(/(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})/, "$3/$2/$1");
        return $.tablesorter.formatFloat(new Date(s).getTime());
    },
    type: "numeric"
});

Then you just need to apply that format to the columns you want, as Gabe G exposed (For example to assign this sorter to the first column you should do the following:

$("#mytable").tablesorter( 
    {   dateFormat: 'dd/mm/yyyy', 
        headers: 
            {
                0:{sorter:'datetime'}
            } 
    } ); 

Croner

Answer 3

You can also add a hidden span tag before your date in numerical format (yyyymmdd). This text will come first and be used for sorting but it will be hidden from sight and only show the format you want.

    <td><span style="display:none">20130923</span>23 September 2013</td>    

Safar

It would be more logical to use a data-* attribute on the table cell, rather than having a span as a child element. – Parnassus

Answer 4

There exist an update for jquery tablesorter plugin.

According to the locale of the your application, you can sort the dates by this update.

You can view the update of the tablesorter by following the below link.

http://tablesorter.openwerk.de/

Intermediacy

Answer 5

Easier way use:

dateFormat:'mm/dd/yyyy hh:mm:ss'

Gooding

Answer 6

To be honest the simplest solution for me was, as compsmart said, adding some hidden text in front of the actual date.

• dateFormat: 'uk' was not working for me, maybe because my date format is again different
• http://tablesorter.openwerk.de/ involves modifying the CSS, first I don't understand why and second the effort is bigger than simple adding hidden text in front of the date.

I like the KISS solution from compsmart!

Pontifical

Answer 7

http://mottie.github.io/tablesorter/docs/

Set the date format. Here are the available options. (Modified v2.0.23).

• "mmddyyyy" (default)
• "ddmmyyyy"
• "yyyymmdd"

In previous versions, this option was set as "us", "uk" or "dd/mm/yy". This option was modified to better fit needed date formats. It will only work with four digit years!

The sorter should be set to "shortDate" and the date format can be set in the "dateFormat" option or set for a specific columns within the "headers" option. See the demo page to see it working.

$(function(){
  $("table").tablesorter({

    dateFormat : "mmddyyyy", // default date format

    // or to change the format for specific columns,
    // add the dateFormat to the headers option:
    headers: {
      0: { sorter: "shortDate" }, // "shortDate" with the default dateFormat above
      1: { sorter: "shortDate", dateFormat: "ddmmyyyy" }, // day first format
      2: { sorter: "shortDate", dateFormat: "yyyymmdd" }  // year first format
    }

  });
}); 

Individual columns can be modified by adding the following (they all do the same thing), set in order of priority (Modified v2.3.1):

• jQuery data data-dateFormat="mmddyyyy".
• metadata class="{ dateFormat: 'mmddyyyy'}". This requires the metadata plugin.
• headers option headers : { 0 : { dateFormat : 'mmddyyyy' } }.
• header class name class="dateFormat-mmddyyyy". Overall dateFormat option.

In my case I have used

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

for the version.

Muir

Answer 8

This was the answer for me:

<td data-order=<fmt:formatDate pattern = "yyyy-MM-dd" value = "${myObject.myDate}" />>${myObject.myDate}</td>

more details, here: https://datatables.net/manual/data/

Sciurine