Date Sorting

mbowers007

I have a column of dates using the ISO 8601 International Calendar Date Format of YYYY-MM-DD (http://www.iso.org/iso/date_and_time_format). I cannot get them to sort 'desc' properly.

Example dates in the markup:
row1: 2009-10-29
row2: 2009-11-04
row3: 2009-10-02
row4: 2009-10-01

When the table initializes, the rows above are sorted in this display order:
row1: 2009-10-29
row2: 2009-10-02
row3: 2009-11-04
row4: 2009-10-01

Nothing I try seems to work. Any help would be greatly appreciated.

allan

Hi mbowers007,

It sounds like this isn't a format that Date.parse() in Javascript will pick up correctly (annoyingly). As such you'll need to use a sorting plug-in designed for your data. Something like http://datatables.net/plug-ins/sorting#uk_date will (almost) do the trick. You'll need a slight modification :-). Also this example might prove useful for how to use plug-ins: http://datatables.net/examples/api/sorting_plugin.html

Regards,
Allan

mbowers007

I got it to work. My problem was two-fold: (1) the ISO date format itself, and (2) my TDs contain markup in addition to the data values. I control my column widths, in a cross-browser manner, so that they remain a fixed width no matter what content they contain...long text blocks, long URLs, etc.
Example:
[code]
Lorem ipsum dolor sit amet, consectetur adipiscing elit. www.mguhlin.org/2008/10/party-city-pumpkin-stencils-and.html
[/code]
And the associated CSS:
[code]
#datatable td .col3 {
word-wrap: break-word;
overflow: hidden;
}
#datatable td .col3 { width: 150px; }
[/code]
The date column does not have a width control issue since the dates are always in the ISO format, but it does also contain a link that points to a detail page.
Example:
[code]
2009-11-04
[/code]
While modifying the UKdate sort functions to suit my ISO date needs, I noticed the passed-in parameter strings were like this:
[code]
2009-11-04
[/code]
So I added a replace function to transform the parameters.
Final result:
[code]
$(document).ready(function() {
$.getScript("/denr-001-theme/javascript/jquery.dataTables.js", function() {

jQuery.fn.dataTableExt.aTypes.push(function(sData) {
// YYYY-MM-DD www.iso.org/iso/date_and_time_format
return 'iso8601date';
});

// in the two plugin extension functions below, the 'replace' function
// changes the passed-in parameter strings...
// from this example:
// 2009-11-04
// to this: 2009-11-04
// (normally, dataTable.js expects the TDs to only contain data
// and not extra markup)
jQuery.fn.dataTableExt.oSort['iso8601date-asc'] = function(a,b) {
a = a.replace(/\n*<.*>\n*<.*>(.*)<.*>\n*\s*<.*>/,"$1");
b = b.replace(/\n*<.*>\n*<.*>(.*)<.*>\n*\s*<.*>/,"$1");
var isoDatea = a.split('-');
var isoDateb = b.split('-');
var x = (isoDatea[0] + isoDatea[1] + isoDatea[2]);
var y = (isoDateb[0] + isoDateb[1] + isoDateb[2]);
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};

jQuery.fn.dataTableExt.oSort['iso8601date-desc'] = function(a,b) {
a = a.replace(/\n*<.*>\n*<.*>(.*)<.*>\n*\s*<.*>/,"$1");
b = b.replace(/\n*<.*>\n*<.*>(.*)<.*>\n*\s*<.*>/,"$1");
var isoDatea = a.split('-');
var isoDateb = b.split('-');
var x = (isoDatea[0] + isoDatea[1] + isoDatea[2]);
var y = (isoDateb[0] + isoDateb[1] + isoDateb[2]);
return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};

$('#datatable').dataTable({
"aoColumns": [
{ "sType": "iso8601date" }, // column 1 -> the Event Date
{ "sType": "string" },
{ "sType": "string" },
{ "sType": "string" },
{ "sType": "string" },
{ "sType": "string" }
],
"aaSorting": [ [0,'desc'], [1,'asc'] ]
});
});
});
[/code]

mbowers007

P.S. - I did not need to validate the iso8601date in the aTypes ext function for my particular case. I'll leave it as an exercise for someone else.

allan

Hi mbowers007,

Fantastic - good to hear that you got it working - and thanks very much for posting your solution!

Regards,
Allan

mbowers007

Due to how our CMS's XSL template engine outputs the html source code AND also due to browser differences, I had to make the replace expression inside of the sort functions more robust.

OLD:
[code]
a = a.replace(/^<.*><.*>(.*)<.*><.*>/,"$1");
b = b.replace(/^<.*><.*>(.*)<.*><.*>/,"$1");
[/code]
NEW:
[code]
a = a.replace(/\n*<.*>\n*<.*>(.*)<.*>\n*\s*<.*>/,"$1");
b = b.replace(/\n*<.*>\n*<.*>(.*)<.*>\n*\s*<.*>/,"$1");
[/code]