UK date problem

tickedon

I am attempting to have my UK dates sorted, I'm using the two date plugins, code:

[code]
jQuery.fn.dataTableExt.aTypes.push(
function ( sData )
{
if (sData.match(/^(0[1-9]|[12][0-9]|3[01])\-(0[1-9]|1[012])\-(19|20|21)\d\d$/))
{
return 'uk_date';
}
return null;
} );

jQuery.fn.dataTableExt.oSort['uk_date-asc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');

var x = parseInt(ukDatea[2] + ukDatea[1] + ukDatea[0]);
var y = parseInt(ukDateb[2] + ukDateb[1] + ukDateb[0]);

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

jQuery.fn.dataTableExt.oSort['uk_date-desc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');

var x = parseInt(ukDatea[2] + ukDatea[1] + ukDatea[0]);
var y = parseInt(ukDateb[2] + ukDateb[1] + ukDateb[0]);

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

$(document).ready( function() {
$('#example').dataTable( {
"bJQueryUI": true,
"iDisplayLength": 50,
"sPaginationType": "full_numbers"

} );
} );
[/code]

I have read http://datatables.net/forums/comments.php?DiscussionID=562&page=1#Item_0 but nothing there seemed to help.

My problem is that dates are being sorted like:

30/10/09
29/10/09
27/09/09
27/06/09
27/03/09
26/10/09

I would expect/like:
30/10/09
29/10/09
26/10/09
27/09/09
27/06/09
27/03/09

It is not sorting on the month or year, only the day. I've tried both YY and YYYY. Really would like to get this working for the local charity I'm helping, so any help much appreciated.

It's on an intranet, so I'm afraid I can't provide a link.

izzy6150

Hi tickedon,

Have you tryed using the 'sSortDataType' setup in the 'aoColumns' initializing settings?
I found that when adding new datatypes and dataType sorting functions that it seemed to always select the (already entered) data types (I guess because they are first in the list of possible data types for auto detection).
So in your example try using the following:
[code]
$('#example').dataTable( {
"bJQueryUI": true,
"iDisplayLength": 50,
"sPaginationType": "full_numbers",
"aoColumns": {
["sSortDataType": "uk_date"], // Column 1 dataType = uk_date
null, // Column 2 use default
["sSortDataType": "uk_date"] // Column n dataType = uk_date
}
} );
[/code]

Hope this helps.

Regards,
Izzy

izzy6150

ooops, got brackets wrong way round :-)

This is what I meant
[code]
$('#example').dataTable( {
"bJQueryUI": true,
"iDisplayLength": 50,
"sPaginationType": "full_numbers",
"aoColumns": [
{"sSortDataType": "uk_date"}, // Column 1 dataType = uk_date
null, // Column 2 use default
{"sSortDataType": "uk_date"} // Column n dataType = uk_date
]
} );
[/code]

Regards,
Izzy

tickedon

Hi izzy,

Thanks for replying. I had thought about doing it this way, but I didn't really understand the format - so thanks for that example with comments!

If it comes down to it, I guess I could do it that way. However, I use the datatable in multiple locations and the date column often isn't in the same position, so it would be a pain to manually setup each table.

Do you know if it would be possible to delete the the US / other date types so that the only viable option left would be UK date?

Many thanks.

izzy6150

Hi there,

Not sure if this will work but you could always try doing the following for the dataType detection code
[code]
jQuery.fn.dataTableExt.aTypes.unshift(
function ( sData )
{
if (sData.match(/^(0[1-9]|[12][0-9]|3[01])\-(0[1-9]|1[012])\-(19|20|21)\d\d$/))
{
return 'uk_date';
}
return null;
} );
[/code]

notice I am using unshift instead of push (this should put the uk_date function at the forfront of all others).

Not sure if it will work tho.

Regards,
Izzy

tickedon

Hi Izzy,

It worked! Many many thanks for your help, while I have no problem with PHP, Javascript is Greek to me!

Cheers,
Alasdair.

izzy6150

Hi tickedon,

Great, glad it worked as required. Have fun now :-)

Regards,
Izzy

stevendobbelaere

[code]
$.fn.dataTableExt.aTypes.push(
function ( sData )
{
if (sData.match(/^(0[1-9]|[12][0-9]|3[01])\-(0[1-9]|1[012])\-(19|20|21)\d\d$/))
{
return 'uk_date';
}
return null;
}
);

$.fn.dataTableExt.oSort['uk_date-asc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');

var x = parseInt(ukDatea[2] + ukDatea[1] + ukDatea[0]);
var y = parseInt(ukDateb[2] + ukDateb[1] + ukDateb[0]);

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

$.fn.dataTableExt.oSort['uk_date-desc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');

var x = parseInt(ukDatea[2] + ukDatea[1] + ukDatea[0]);
var y = parseInt(ukDateb[2] + ukDateb[1] + ukDateb[0]);

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

$(document).ready(function() {
$('#dtable').dataTable( {
"bPaginate":false,
"bAutoWidth":true,
"sDom": 'f<"">rt<"top"pi>',
"bStateSave": true,

});
});
[/code]

I'm using this code to sort my table with the first column in the format dd/mm/yy
All it does, is just sort by day even when I declare the uk_date type to the first column

e.g.
01/02/09
02/01/09
03/08/10

Any help?

stevendobbelaere

ok I figured it out now.

instead of "sSortDataType" I now used the sType and that seems to work for me.
This code works:

[code]
$.fn.dataTableExt.aTypes.push(
function ( sData )
{
if (sData.match(/^(0[1-9]|[12][0-9]|3[01])\-(0[1-9]|1[012])\-(19|20|21)\d\d$/))
{
return 'uk_date';
}
return null;
}
);

$.fn.dataTableExt.oSort['uk_date-asc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');

var x = parseInt(ukDatea[2] + ukDatea[1] + ukDatea[0]);
var y = parseInt(ukDateb[2] + ukDateb[1] + ukDateb[0]);

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

$.fn.dataTableExt.oSort['uk_date-desc'] = function(a,b) {
var ukDatea = a.split('/');
var ukDateb = b.split('/');

var x = parseInt(ukDatea[2] + ukDatea[1] + ukDatea[0]);
var y = parseInt(ukDateb[2] + ukDateb[1] + ukDateb[0]);

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

$(document).ready(function() {
$('#dtable').dataTable( {
"bPaginate":false,
"bAutoWidth":true,
"sDom": 'f<"">rt<"top"pi>',
"bStateSave": true,
"aoColumns": [
{"sType": "uk_date"},
null,
null,
null,
null,
null
]
});
});

[/code]

izzy6150

Hi there,

Glad you managed to figure it out. There is another alternative that will allow dataTables to auto select the correct type for you so you dont have to always specify the column type. To do that you can use the code i posted above where instead of using 'push' you use 'unshift' as here:
[code]
jQuery.fn.dataTableExt.aTypes.unshift(
function ( sData )
{
if (sData.match(/^(0[1-9]|[12][0-9]|3[01])\-(0[1-9]|1[012])\-(19|20|21)\d\d$/))
{
return 'uk_date';
}
return null;
} );
[/code]

Regards,
Izzy

Dunhamzzz

Also had the problem with uk_date sorting, was unable to get the auto type detection or declaring aoColumns working, using "sType" instead of "sSortDataType" in the "aoColumns" declaration as suggested above by stevendobbelaere is what fixed it for me.

pash

Hm,
same to me with date-euro plugin:

"sSortDataType" : "date-euro"

didn't work out but

using sType instead worked.

Is this a BUG?

allan

No, sType is the correct one to use for the basic sort functions, as used in the various demos and the 'how to use sorting plug-ins' documentation: http://datatables.net/plug-ins/sorting#how_to_type .

Allan