Sorting Plug-In > MM/DD/YYYY hh:mm:ss

elite-rob

There are several sorting plug-ins that already exist for the amazing DataTables script, which can be seen here: http://datatables.net/plug-ins/sorting

Unfortunately, the date/time format that I use (and many others use) is: MM/DD/YYYY hh:mm:ss

And, for the moment, there is no plug-in that plays nicely with that format.

I suspect one of the existing date plug-ins could be modified to match this format. There is already one for "dd/mm/YYY hh:ii:ss" and that is very similar. I don't really know JS that well, otherwise I would have attempted this on my own and then shared the results with everyone.

Does anyone out there want to take a crack at creating this plug-in? I am confident it would be used by many people.... I definitely don't want to seem selfish and say this is just for me. =)

28.vivek.s

ya...sure .....why not?

LionHeart

Salut,
J'ai jet

elite-rob

Hi LionHeart,

My sincere apologies for the delay in replying to your post.

Thank you *soo* much for your help. It is greatly appreciated.

Also, once I get this working, I think we should have Allan add it to the "Sorting Plug-Ins" page so other people can use this as well.

Unfortunately, despite my best efforts to follow your instruction, I cannot seem to get this to work.

I made the changes you suggested, so the script now looks like this:

[code]
$(document).ready(function(){

function trim(str) {
str = str.replace(/^\s+/, '');
for (var i = str.length - 1; i >= 0; i--) {
if (/\S/.test(str.charAt(i))) {
str = str.substring(0, i + 1);
break;
}
}
return str;
}

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

if (trim(b) != '') {
var frDateb = trim(b).split(' ');
var frTimeb = frDateb[1].split(':');
frDateb = frDateb[0].split('/');
var y = (frDatea2[2] + frDatea2[0] + frDatea2[1] + frTimea[0] + frTimea[1] + frTimea[2]) * 1;
} else {
var y = 10000000000000;
}
var z = ((x < y) ? -1 : ((x > y) ? 1 : 0));
return z;
};

jQuery.fn.dataTableExt.oSort['date-euro-desc'] = function(a, b) {
if (trim(a) != '') {
var frDatea = trim(a).split(' ');
var frTimea = frDatea[1].split(':');
var frDatea2 = frDatea[0].split('/');
var x = (frDatea2[2] + frDatea2[0] + frDatea2[1] + frTimea[0] + frTimea[1] + frTimea[2]) * 1;
} else {
var x = 10000000000000;
}

if (trim(b) != '') {
var frDateb = trim(b).split(' ');
var frTimeb = frDateb[1].split(':');
frDateb = frDateb[0].split('/');
var y = (frDatea2[2] + frDatea2[0] + frDatea2[1] + frTimea[0] + frTimea[1] + frTimea[2]) * 1;
} else {
var y = 10000000000000;
}
var z = ((x < y) ? 1 : ((x > y) ? -1 : 0));
return z;
};

[/code]


Then, of course I initialize the dataTable with this:

[code]
var oTable = $('#ctl00_ContentPlaceHolder1_gvMySurvey').dataTable({
"aoColumns": [
{ "sType": "html" },
null,
{ "sSortDataType": "date-euro-asc",
"sType": "html" },
{ "bSortable": false,
"bSearchable": false},
{ "bSortable": false,
"bSearchable": false},
{ "bSortable": false,
"bSearchable": false},
null
],
"bJQueryUI": false,
"sPaginationType": "full_numbers",
"aaSorting": [ ] // Prevents initial sorting
});
[/code]


I'm not entirely sure if this is correct:
{ "sSortDataType": "date-euro-asc", "sType": "html" },

That is the column (column #3) that I am trying to do the date sorting on.
I tried different variations, but wasn't able to get it to work.


Any other ideas? I'm up for trying anything to get this to work. =)

Thanks!

In case you want to see the file I'm using, you can download it here:
http://www.4shared.com/document/jbhFTgrK/dataTables_Date_Sorting_Glitch.html

allan

Try using:

[code]
"sType": "date-euro"
[/code]
instead (and no sSortDataType)

Allan

elite-rob

Hi Allan,

Thanks for the reply. As always, I greatly appreciate your help. I definitely think (once we get it working), it'd be worth adding this to the sorting page for others to use. I doubt I'm the only one using this type of format and others should benefit from these efforts. =)

I changed it around so I now have this:

[code]
var oTable = $('#ctl00_ContentPlaceHolder1_gvMySurvey').dataTable({
"aoColumns": [
{ "sType": "html" },
null,
{ "sType": "date-euro",
"sType": "html" },
{ "bSortable": false,
"bSearchable": false},
{ "bSortable": false,
"bSearchable": false},
{ "bSortable": false,
"bSearchable": false},
null
],
"bJQueryUI": false,
"sPaginationType": "full_numbers",
"aaSorting": [ ] // Prevents initial sorting
});
[/code]


The problem is that I'm still not getting the expected results based on the various dates/times.

As I sort the "Created" column I would expect it to render results like this:

1/6/2011 4:32:16 AM
1/1/2011 9:00:46 AM
12/13/2010 5:12:47 AM
5/26/2010 6:36:04
5/26/2010 6:35:44

OR

5/26/2010 6:35:44 AM
5/26/2010 6:36:04 AM
12/13/2010 5:12:47 AM
1/1/2011 9:00:46 AM
1/6/2011 4:32:16 AM

But, if you download my file, you'll see it doesn't sort this way.

I'm not sure if I implemented LionHeart's instructions wrong, or if I'm making a mistake somewhere else... or if there is some sort of other bug. I kind of assume it's something I'm doing wrong, I'm just not sure what.

Thanks in advance!

Updated Download URL: http://www.4shared.com/document/3B9RwjKo/dataTables_Date_Sorting_Glitch.html

allan

You've defined sType twice in one object:

[code]
"sType": "date-euro",
"sType": "html"
[/code]
That would be like saying:

[code]
var a = 1;
a = 2;
[/code]
and expecting a to hold both 1 and 2. If you want to use HTML stripping with the euro sorting type, you'll need a plug-in which specifically does that (i.e. strip the HTML and then does the sort). A modification of the code above and the HTML stripping like what DataTables does (x.replace( /<.*?>/g, "" ).toLowerCase()) would to the trick.

Also yes, I'll look at adding this to the sorting plug-ins page.

Allan

elite-rob

Hi Allan,

Thanks so much for pointing out my _goofy_ mistake.

That makes total sense.

I'm admittedly a JS newbie, so I apologize for making these trivial errors... although, I am trying my best and I greatly appreciate yours & others help in this forum.

I understand that I need to edit the plug-in JS so that it does both HTML stripping and Euro-Sorting Type.

I know you said this "(x.replace( /<.*?>/g, "" ).toLowerCase()) would to the trick.", but I'm totally lost about where I insert that.

Again, I apologize for being a newbie and asking a question that I'm sure has an obvious answer, but a push in the right direction would be incredibly appreciated. I am just so eager to get this working! =)


On a different note, that's great that you'll take a look at adding this to the sorting plug-ins page. At least this way other people using this awesome script will have an easy way to sort based on this date format.

Cheers!

LionHeart

Saluts les cocos,
Je vois plusieurs soucis dans tout

elite-rob

Hi LionHeart,

You are truly amazing!

Je suis d

LionHeart

H

elite-rob

Merci! Merci! Merci!

That file is P E R F E C T ! ! ! =)

Once again, thanks to both you and Allan for your help. I truly could not have done this without you and I am very grateful.

anjibman

Is there any update link to get code for this Plug-in?
I am trying to sort column of format MM/dd/yyy hh:mm:ss a (9/29/2011 1:52:34 PM)

allan

http://datatables.net/plug-ins/sorting#date_euro_full :-)

Allan

pweil

Was this project ever successful? I need to sort two date columns by mm/dd/yyyy. The link provided in the last post is for a plugin that does dd/mm/yyyy, not mm/dd/yyyy. Or am I mistaken? I'm a little surprised that something like this isn't easier to find, unless I'm overlooking something. Thanks!