issue with date sort for "mmm dd yyyy format"

gurusudhakar

Hello,

I am having troubles with datatable while sorting of date column. The date format being used is "Mmm DD YYYY" e.g. "Dec 23 2011". Initially I explored this forum to get suggestions on date sorting plugin and zeroed upon using Natural sort http://www.overset.com/2008/09/01/javascript-natural-sort-algorithm-with-unicode-support. Natural sort worked well for most of the scenarios except for one: It is not able to do justice with the year part of my dates array.

Ex:- if I’ve dates from oct2011 - Feb2012 like 'Oct 01 2011', 'Nov 01 2011', 'Dec 01 2011', 'Jan 01 2012', 'Feb 01 2012'.
Then I expect the sorted data (In descending order) to look like: 'Feb 01 2012', 'Jan 01 2012', 'Dec 01 2011' ,'Nov 01 2011', 'Oct 01 2011'.
But what I actually get is : 'Dec 01 2011', 'Oct 01 2011', 'Nov 01 2011', 'Feb 01 2012', 'Jan 01 2012'.

While sorting, I first covert ‘Mmm’ part of the date into numeric equivalent e.g. 'Oct'->10, ‘Dec'-> 12 and so on. I understand that natural sort works by splitting each string to compare for sorting into an array in the appropriate order based off blocks of strings and blocks of numbers. Therefore my string ‘Oct 01 2011’ will be broken into ‘Oct’->’10’, ‘01’, ‘2011’ and each block will be compared to equivalent block in another date in left to right order. Therefore, YYYY gets compared in the end whereas it carries highest face value in the date field and it should be compared first.

Any hints are highly appreciated. I cannot consider changing the date format as the customer is very particular about it.

Thanks,
GS.

gurusudhakar

Any ideas to fix this issue, please help.

gurusudhakar

Found the way to sort "mmm dd yyyy"date format sorting:

the code is below.
// Custom date sorting functions for ascending sorting of dates in "MMM dd yyyy" format*/
jQuery.fn.dataTableExt.oSort['date-custom-asc'] = function(a,b) {
// Making sure to remove any HTML code sorrounding the value to be sorted.
var date1 = a.replace( /<.*?>/g, "" ).replace(/^\s+|\s+$/g,"");
var date2 = b.replace( /<.*?>/g, "" ).replace(/^\s+|\s+$/g,"");

//Split date fields on white space to get MMM, dd and YYYY as array
var dateTokens1 = date1.split(' ');
var dateTokens2 = date2.split(' ');

var x = (dateTokens1[2] + getMonthIdx(dateTokens1[0]) + dateTokens1[1]) * 1;
var y = (dateTokens2[2] + getMonthIdx(dateTokens2[0]) + dateTokens2[1]) * 1;

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

// Custom date sorting functions for descending sorting of dates in "MMM dd yyyy" format*/
jQuery.fn.dataTableExt.oSort['date-custom-desc'] = function(a,b) {
// Making sure to remove any HTML code sorrounding the value to be sorted.
var date1 = a.replace( /<.*?>/g, "" ).replace(/^\s+|\s+$/g,"");
var date2 = b.replace( /<.*?>/g, "" ).replace(/^\s+|\s+$/g,"");

//Split date fields on white space to get MMM, dd and YYYY as array
var dateTokens1 = date1.split(' ');
var dateTokens2 = date2.split(' ');

var x = (dateTokens1[2] + getMonthIdx(dateTokens1[0]) + dateTokens1[1]) * 1;
var y = (dateTokens2[2] + getMonthIdx(dateTokens2[0]) + dateTokens2[1]) * 1;

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

it helps if any one using same kind of date format.

Thanks,
Gs