Need help for sorting date with dd/mm/yyyy format

jd-webdesign

Hi,

I have a table with 4 columns. The last one contains date (dd/mm/yyyy format).

But the date doesn't sort "properly". Can anyone help me ? (i'm completely new to javascript).

Here's a part of my code :

[code]



$(document).ready(function() {
$('#mytable').dataTable( {
"bJQueryUI": true,
"oLanguage": {
"sUrl": "table/language/dataTables.french.txt"
},
"sPaginationType": "full_numbers",
"aoColumns": [
{"bSortable": true},
{"bSortable": false},
null,
null
]
} );
} );




#
Ecouter
Nom
Date




1
xxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
01/09/2010
[/code]

What I have to add for sorting date and where ?

In advance thanks,

JD

deus_pater

To solve this problem, I added a hidden column in YYYYMMDD format, and pointed my DD/MM/YYYY date column to sort on the hidden column.

So, from your example above:

[code]

$(document).ready(function() {
$('#podcastscitematin').dataTable( {
"bJQueryUI": true,
"oLanguage": {
"sUrl": "table/language/dataTables.french.txt"
},
"sPaginationType": "full_numbers",
"aoColumns": [
{"bSortable": true},
{"bSortable": false},
null,
{"iDataSort": 4},
{"bVisible": false}
]
} );
} );





#
Ecouter
Nom
Date
Sortable Date




1
xxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
01/09/2010
20100901

[/code]

There may be better ways, but this worked for me.

Hope that helps,
Mayank

jd-webdesign

Hi,

Thanks a lot for your time.

I think your idea is good and works but maybe a little bit "heavy" to do if the table have a lot of datas.

If anyone have an another solution it could be so cool.

Anyway thanks a lot deus_pater

deus_pater

There's more discussion of this topic here:
http://datatables.net/forums/comments.php?DiscussionID=1952&page=1

Including how to write a custom sort function.

Hope that helps!

jd-webdesign

Hi thanks again,

I think I have to add a plug-in like here :

http://datatables.net/plug-ins/sorting

But I'm not sure how to add this to my code ?

JD

jd-webdesign

Hi,

I try this but it doesn't work :

HTML code :

[code]




$(document).ready(function() {
$('#mytable').dataTable( {
"bJQueryUI": true,
"oLanguage": {
"sUrl": "table/language/dataTables.french.txt"
},
"sPaginationType": "full_numbers",
"aoColumns": [
{"bSortable": true},
{"bSortable": false},
null,
{"sType": "date-euro"},
null
]
} );
} );




#
Ecouter
Nom
Date




1
xxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
01/09/2010[/code]

I add the plugin from Ronan Guilloux found on this page http://www.datatables.net/plug-ins/sorting but the sorting doesn't work.

The JS code :

[code]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[1] + frDatea2[0] + 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 = (frDateb[2] + frDateb[1] + frDateb[0] + frTimeb[0] + frTimeb[1] + frTimeb[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[1] + frDatea2[0] + 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 = (frDateb[2] + frDateb[1] + frDateb[0] + frTimeb[0] + frTimeb[1] + frTimeb[2]) * 1;
} else {
var y = 10000000000000;
}
var z = ((x < y) ? 1 : ((x > y) ? -1 : 0));
return z;
}; [/code]

What I'm doing wrong ?

Thanks,

JD

deus_pater

Hi JD,

At the bottom of the plugins page, there's a "how to use" section -- I haven't tried it, but it looks like you might need to use "sSortDataType" set to "dom-text" in addition to "sType" in your columns spec.

Hope that helps,
Mayank

jd-webdesign

I found my problem it's just because I made a mistake with the placement of "sType" (not assigned to the right column).

Now everything works great and the sorting of my date column works like a charm:)

kkudi

go to datejs.com and download the javascript file from there.

it overrides Date.parse and it pretty much caters for all scenarios above.

Allan's code uses Date.parse to sort columns with sType date so it works pretty well.

Maybe not as fast as Date.parse native function but still does the trick. Otherwise just a format which is parsable by all browsers.

PaoloValladolid

datejs.com is noticeably slower than the default Date.parse. OTOH, it gets the dates sorted correctly.

ComboFusion

I would like to add that the plugin mentioned by jd-webdesign DOES NOT sort date-time properly.

melanke

i`ve made a plugin for sorting date dd/mm/yyyy hh:mm
and it works properly

[code]

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;
}

function dateHeight(dateStr){
if (trim(dateStr) != '') {
var frDate = trim(dateStr).split(' ');
var frTime = frDate[1].split(':');
var frDateParts = frDate[0].split('/');
var day = frDateParts[0] * 60 * 24;
var month = frDateParts[1] * 60 * 24 * 31;
var year = frDateParts[2] * 60 * 24 * 366;
var hour = frTime[0] * 60;
var minutes = frTime[1];
var x = day+month+year+hour+minutes;
} else {
var x = 99999999999999999; //GoHorse!
}
return x;
}

jQuery.fn.dataTableExt.oSort['date-euro-asc'] = function(a, b) {
var x = dateHeight(a);
var y = dateHeight(b);
var z = ((x < y) ? -1 : ((x > y) ? 1 : 0));
return z;
};

jQuery.fn.dataTableExt.oSort['date-euro-desc'] = function(a, b) {
var x = dateHeight(a);
var y = dateHeight(b);
var z = ((x < y) ? 1 : ((x > y) ? -1 : 0));
return z;
};

$(function(){

$("table").dataTable({
"aoColumns": [
{ "sType": "date-euro"},
null,
null,
null,
null
]
});

});


[/code]

gsgill76

I had same issue of sorting (US-Style DateTime Field)
[quote]deus_pater[/quote] you solve my issue.... Thanks a-lot

Ashishus

deus_pater


Please explain me in details , how to use hidden column and sort the corresponding date column .

Danny

Thank's melanke. It run ok, but I have a column with dontSort and now it doesn't work.

Danny

OK, I have a solution if you need a noSorts columns and a colum with sorting date dd/mm/yyyy hh:mm.
You need melanke's code and this:
[code]
var dontSort = [];
var id="#large";

$(id+' thead th').each( function () {
if ( $(this).hasClass( 'no_sort' )) {
dontSort.push( { "bSortable": false } );
} else {
if($(this).hasClass( 'date' )){
dontSort.push({ "sType": "date-euro"});
}
else{
dontSort.push( null );
}
}
} );
$(id).dataTable( {
"aoColumns": dontSort
} );

//Now in thead you can write two class (no_sort or date)

field1
field2
Fecha
[/code]

Gangadhar

Thanks "melanke" for your code.

jheaddon

Hi guys,

Any chance somebody could modify the to support columns in the format 'ddd dd/MM/yy HH:mm:ss' without the use of a hidden sort column?

For example:

'Mon 20/01/12 20:20:20'


Cheers!

jheaddon

Nevermind I figured it out, for anyone interested you need to use melanke's code above and change the following function:

// Splits the provided input string in to format dd/MM/yy HH:mm:ss
function dateHeight(inputString)
{
var result = 99999999999999999;

if (trim(inputString) != '')
{
var frDate = trim(inputString).split(' ');
var frTime = frDate[2].split(':');
var frDateParts = frDate[1].split('/');
var day = frDateParts[0] * 60 * 24;
var month = frDateParts[1] * 60 * 24 * 31;
var year = frDateParts[2] * 60 * 24 * 366;
var hour = frTime[0] * 60;
var minutes = frTime[1];
var seconds = frTime[2];
result = day + month + year + hour + minutes + seconds;
}

return result;
};

annoyingmouse

I'm sorry to hijack this but I've spent 3 hours on this and I'm getting nowhere.

I initiate a datatable thus:

[code]
var myTable = $('#inspectionsTable').dataTable({
"bRetrieve": true,
"bAutoWidth": false,
"aoColumns": [
{ sWidth: '10%'},
{ sWidth: '30%'},
{ sWidth: '10%'},
{ sWidth: '10%'},
{ sWidth: '10%'},
{ sWidth: '10%'},
{ sWidth: '10%'},
{ sWidth: '10%'}
],
aoColumnDefs: {
target: [2], // change to whichever column you need. indexed at 0
sType: "date-eu"
}
});
[/code]

I then call some data from the server and iterate over it to fill the rows:

[code]
$.each(inspections.records, function(index, value){
var tempDateString = sanitiseNulls(value.Requested_Date__c);
var bits = tempDateString.split("-");
var dateString = bits[2]+"/"+bits[1]+"/"+bits[0];
myTable.fnAddData([
sanitiseNulls(value.Application__r.Name),
sanitiseNulls(value.Application__r.FullAddress__c),
dateString,
sanitiseNulls(value.Application__r.Proposed_Works__c),
sanitiseNulls(value.Application__r.BCOfficer__r.Name),
sanitiseNulls(value.Application__r.Proposed_Works__c),
sanitiseNulls(value.Application__r.Applicant__r != null ? value.Application__r.Applicant__r.Name : "None"),
sanitiseNulls(value.Application__r.Applicant__r != null ? value.Application__r.Applicant__r.Phone : "None")
]);
});
[/code]

But when I call

[code]
myTable.fnSort([[2,'asc']]);
[/code]

after making sure I've got this:

[code]
jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-eu-pre": function ( date ) {
var date = date.replace(" ", "");
if(date.length > 0){
if (date.indexOf('.') > 0) {
/*date a, format dd.mn.(yyyy) ; (year is optional)*/
var eu_date = date.split('.');
} else {
/*date a, format dd/mn/(yyyy) ; (year is optional)*/
var eu_date = date.split('/');
}
/*year (optional)*/
if (eu_date[2]) {
var year = eu_date[2];
} else {
var year = 0;
}
/*month*/
var month = eu_date[1];
if (month.length == 1) {
month = 0+month;
}
/*day*/
var day = eu_date[0];
if (day.length == 1) {
day = 0+day;
}
}
return (year + month + day) * 1;
},
"date-eu-asc": function ( a, b ) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},
"date-eu-desc": function ( a, b ) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});
[/code]

It sorts on day, then month then year.

So I get:

03/03/2012
04/04/2012
05/03/2012

rather than

03/03/2012
05/03/2012
04/04/2012

If anyone could tell me what I'm doing wrong, and hopefully how to correct it, I'd happily buy them a pint the next time they're in Cambridge ;-)

Cheers,

Dom

icsnerdics

just my two cents

[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(':'); // hh:mm:ss

var frDatea2 = frDatea[0].split('-'); // YYYY-MM-DD

var x = new Date(frDatea2[2],frDatea2[1]-1,frDatea2[0],frTimea[0],frTimea[1],frTimea[2]);

} else {

var x = new Date(0,0,0,0,0,0);

}

return x;
},

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

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

[/code]

why reinvent the wheel

surajitdey

I've data like:

17-09-2012 02:30:00
19-10-2012 09:54:32
19-10-2012 10:16:32
19-10-2012 10:18:34
19-10-2012 10:46:57
19-10-2012 10:48:38
25-10-2012 12:30:51
25-10-2012 13:57:04
26-09-2012 09:45:12

I'm suing datable, but I can't sort this data properly. When click on sort link 'arrow(up/down)' its always sort like that.But I want the datetime field column properly sort. I've used above example in my application.js file but it doesn't work. I'm working rails application. If anyone help me to fix this asap, it is very urgent to me to fix this one.

advance thnx

axayac

I dont find the link for download this plug in http://www.datatables.net/plug-ins/sorting

May be i need glasses

Thank you for your help

Pascal

khushal

sorting on dd/mm/yyyy or dd/mmm/yyyy or mmm/yyyy basis
[code]// Override default implementation for date sorting
jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-uk-pre": function ( a ) {
var ukDatea = jQuery(a).text().split('-');
if(ukDatea.length==2){
var month = ukDatea[0];
}else{
month = ukDatea[1];
}
if(month=="jan" || month=="Jan"){month=01;}
else if(month=="feb" || month=="Feb"){month=02;}
else if(month=="mar" || month=="Mar"){month=03;}
else if(month=="apr" || month=="Apr"){month=04;}
else if(month=="may" || month=="May"){month=05;}
else if(month=="jun" || month=="Jun"){month=06;}
else if(month=="jul" || month=="Jul"){month=07;}
else if(month=="aug" || month=="Aug"){month=08;}
else if(month=="sep" || month=="Sep"){month=09;}
else if(month=="oct" || month=="Oct"){month=10;}
else if(month=="nov" || month=="Nov"){month=11;}
else{month=12;}
if(ukDatea.length==2){
return (ukDatea[1] + month) * 1;
}else{
return (ukDatea[2] + month + ukDatea[0]) * 1;
}
},

"date-uk-asc": function ( a, b ) {
console.log(a+" "+b);
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},

"date-uk-desc": function ( a, b ) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});[/code]

allan

Excellent - thank you for posting that :-)

Allan

NPradhip

Following works best with dd/MM/yyyy HH:mm:ss format

$(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;
}
} );

caseymarcallen

To handle date times in the format 'd/MM/yyyy h:mm:ss AM/PM' I used the following code:

This splits the string up and then takes into account:
1. Days that are less than 10 (e.g. converting '3' into '03')
2. Converting 12 hour time into 24 hour time (e.g. 11 PM into 23)
3. Hours that are less than 10 (e.g. converting 3 AM into '03')

This will take a date like '3/01/2013 11:14:25 PM' and convert it into '20130103231425'

dateTimeSorting.js
---------------
[code]
$.extend(jQuery.fn.dataTableExt.oSort, {
"datetime-au-pre": function (a) {
var x;
if ($.trim(a) != '') {
var frDatea = $.trim(a).split(' ');
var frTimea = frDatea[1].split(':');
var frDatea2 = frDatea[0].split('/');
var year = frDatea2[2];
var month = frDatea2[1];
var day = frDatea2[0];
var hour = frTimea[0];
var minute = frTimea[1];
var second = frTimea[2];
var ampm = frDatea[2];

if (day < 10) {
day = '0' + day;
}

if (ampm == 'PM' && hour < 12) {
hour = parseInt(hour, 10) + 12;
}

if (hour < 10) {
hour = '0' + hour;
}

x = (year + month + day + hour + minute + second) * 1;
} else {
x = 10000000000000;
}

return x;
},

"datetime-au-asc": function (a, b) {
return a - b;
},

"datetime-au-desc": function (a, b) {
return b - a;
}
});
[/code]

Once you have this in place all you need to do is tell the dataTable what format your column is in. e.g.:

[code]










Title
Assigned DateTime




Title A
25/01/2013 3:11:49 PM


Title B
03/01/2013 10:15:22 AM






$(document).ready(function () {

$('#tblCases').dataTable({aoColumnDefs: [{ "sType": "datetime-au", "aTargets": [1] }]






[/code]

allan

Very nice - thanks for sharing :-)

Allan

miller345

Thanks caseymarcallen

have adapted your solution to my needs. (date formatted as dd/MM/YYYY)

[code]
$.extend(jQuery.fn.dataTableExt.oSort, {
"date-uk-pre": function (a) {
var x;
try {
var dateA = a.replace(/ /g, '').split("/");
var day = parseInt(dateA[0], 10);
var month = parseInt(dateA[1], 10);
var year = parseInt(dateA[2], 10);
var date = new Date(year, month - 1, day)
x = date.getTime();
}
catch (err) {
x = new Date().getTime();
}

return x;
},

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

"date-uk-desc": function (a, b) {
return b - a;
}
});
[/code]

[code]
$("#table").dataTable({
aoColumnDefs: [{ "sType": "date-uk", "aTargets": [0] }]
}
[/code]

FredDagg

There is a simpler way...
Just put your sort order field first in html comments.
It won't appear on the browser but jQuery respects it when sorting.
e.g.
[code] <!-- 20130319--> 19/03/13 [/code]

paulofer85

Ok y tried " <!-- 20130319HHMM--> 19/03/13 HH:mm"
It worked for me :)
The definition of the datatable:
[code]
// jQuery dataTables
$('#datatableRequirementList').dataTable({
"sPaginationType": "full_numbers",
"aoColumns": [
{ "bSortable": false},
{ "bSortable": true },
null,
{ "sType": "string" }, //THIS IS THE DATE
null,
null,
null,
null,
null,
null,
null,
null,
null
]
});
[/code]