sort date format mm/dd/yyyy

triumdh

Hello

I think I have read every discussion on the above but cannot find an answer.
I am trying to sort a column by date in the mm/dd/yyyy format.
There are no spaces or any other data just the date.
I have the sort turned off when the table is first loaded and my MySQL query sorts it by date (newest first). The problem is when I click date header decending I get 31/12/2011 at the top and not 03/15/2012. If I click the header again I get 01/01/2012. It doesn't appear to take the year into account. I have seen the plugins for UK date but this is a US date that MySQL can handle.
Any clues as to where I am going wrong.

Thanks in advance.

[code]
$(document).ready(function() {
oTable = $('#example').dataTable({
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"aaSorting": [ ],
"aoColumnDefs": [
{ "bSearchable": false, "aTargets": [ 6 ] },
{ "bSearchable": false, "aTargets": [ 7 ] }
]
} );
} );
[/code]

The date column is column 5.

allan

> I have seen the plugins for UK date but this is a US date that MySQL can handle.

MySQL will handle that, but since your sorting is being done by Javascript and not MySQL that doesn't matter. It sounds like you need to make a small modification to the UK date sorting plug-in to rearrange the date components from the mm/dd/yyyy format and set the sType for that column to use your sorting plug-in.

Allan

triumdh

Thank you.

triumdh

Allan,

For future reference what would be a good US date format to use with data tables?

Martin.

triumdh

Hello,

This is my first experience with javascript so I am a little lost here. To use the pluggin I take the following code from your website
[code]
function calculate_date(date) {
var date = date.replace(" ", "");

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

jQuery.fn.dataTableExt.oSort['eu_date-asc'] = function(a, b) {
x = calculate_date(a);
y = calculate_date(b);

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

jQuery.fn.dataTableExt.oSort['eu_date-desc'] = function(a, b) {
x = calculate_date(a);
y = calculate_date(b);

return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};
[/code]

Then save it to a file ie data_sort.js
I then add this line
[code]

[/code]
before the document ready function

Then I add the following to the document ready function
[code]
{ "sType": "data_sort", "aTargets": [ 5 ] }
[/code]

I know I have the right document ready function format as the date doesn't do anything when I apply it.

Thank you for your patience. I am taking a javascript tutorial but I think it may be some time before I become proficient enough for this.

allan

Yeah that basically looks correct to me. If you are using Firefox install Firebug and have a look at the console, check to see if you have a JS error - if using Chrome/Safari use the Inspector.

> For future reference what would be a good US date format to use with data tables?

Natively DataTables will pick up anything that can be ready by Date.parse() as built into Javascript. If you are using a format that doesn't automatically pick up, then it really doesn't matter which format you do use, you will need a sorting plug-in, so at that point you can use any format, with any calendar you want :-).

Allan

triumdh

Thank you

Martin

triumdh

I was using div's to center the data in the table.
I replaced the html div's used to center columns within the cells with the DATA which then removed the html and then the sort worked.

Thank you for your time and patience