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

virgil

Hi,

I user date with this format in the table: dd/mm/yyyy (like 09/03/2012)

I tried this like this:
[code]
jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-ro-pre": function ( a ) {
var date = a.replace(" ", "").split('/');
//console.log((date[2] + date[1] + date[0]) * 1);
return (date[2] + date[1] + date[0]) * 1;
},

"date-ro-asc": function ( a, b ) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},

"date-ro-desc": function ( a, b ) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});
[/code]
The js I load after loading the datatables.js and then in the table definition I do this:
[code]
"aoColumnDefs": [
{"bSortable": false, "aTargets": [ 0, 9, 10, 11, 12 ]},
{"sType": "numeric", "aTargets": [ 5, 6, 7 ]},
{"sType": "date-ro", "aTargets": [ 3, 4 ]},

],
[/code]
But it does not seem to work whatever I tried to do.

virgil

It did work without pre and just doing everything in date-ro-desc and date-ro-asc. Thanks

dylanmac

Anyone have solutions for formatting dates the American way: mm/dd/yyyy? I don't see any plugins that support this.

roady

Hi.
Having issue with getting the date sorting to work. Trying your examples with no success :(
- I'm using date format YYYY-mm-dd HH:MM:SS
- Tried to put an alert in the -pre but it's not executed.
- Tried using sType on the column as well

[code]
var oTable;

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"custom-date-pre": function ( a ) {
var aSplit = $.trim(a).split(' ');
var aDate = aSplit[0].split('-');
var aTime = aSplit[1].split(':');
var x = aDate[0] + aDate[1] + aDate[2] + aTime[0] + aTime[1] + aTime[2];
return x;
},

"custom-date-asc": function ( a, b ) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},

"custom-date-desc": function ( a, b ) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
} );

$(document).ready(function() {

// create table
oTable = $('#tblPlayers').dataTable( {
bProcessing: true,
bServerSide: true,
sAjaxSource: "service_players.php",
sPaginationType: "full_numbers",
aoColumns: [
{ bVisible: true }, // uid
{ bVisible: true,
fnRender: function ( oObj ) {
if( oObj.aData[1] == true )
return '';
return '';
} }, // is banned
{ bVisible: true }, // firstname
{ bVisible: true }, // lastname
{ bVisible: true }, // email
{ bVisible: true }, // alias
{ bVisible: true }, // custom uid
{ bVisible: true }, // friend uid
{ bVisible: true }, // country
{ bVisible: true }, // created YYYY-mm-dd HH:MM:SS
{ bVisible: true }, // last login
{ bVisible: true } // num of logins
],
aaSorting: [[9, 'desc']],
aoColumnDefs: [{ sType: "custom-date", aTargets: [9,10] }]
});

});
[/code]

contohsuratindonesia

the easiest way is use: [date in yyyy-mm-dd format] [date in your format]. Work for me :D

mfittko

+1 on the hidden span solution. no js hacking needed here!

allan

DataTables 1.10 is going to include support for the `data-sort` and `data-order` attributes on the cells to make this kind of thing easier!

If you want to try it out before it goes beta you can see it here: https://github.com/DataTables/DataTables/blob/master/examples/advanced_init/html5-data-attributes.html

Allan

sparkplant

Based on last allan comment, I wanted to sort using the custom data right now and couldn t wait for 1.10 so I wrote this litlle plugin.

[code]
$.fn.dataTableExt.afnSortData['dom-data-order'] = function ( oSettings, iColumn )
{
return $.map( oSettings.oApi._fnGetTrNodes(oSettings), function (tr, i) {
return $('td:eq('+iColumn+')', tr).attr('data-order');
} );
}
[/code]

The magic with this approach is that you can decide on the back end hwo it should be sorted and no more need to write one formmater per column

It works with the dom set like this

[code]
31/12/2015
[/code]


and the configuration for columns set like this

[code]
$('.sortable').dataTable({
"aoColumns": [{"sSortDataType": "dom-data-order", "sType": "numeric"}]
})
[/code]


On the backend I format date like this
[code]
$html .= ''.$niceDate.''."\n";
[/code]


Hope it helped

allan

Nice one - thanks for sharing this with us!

Allan

Chooko

In case anyone is still looking for a solution. This works really well for me, for European date format.

If a column is sortable and the data matches (Regex), the sorting will apply:
- 3.2.2014 or
- 3.02.2014 or
- 03.02.2014

I hope this help.

[code]
$(function () {
$.extend($.fn.dataTableExt.oSort, {
"date-eu-pre": function (date) {
var date = /^(\d{1,2})\.(\d{1,2})\.(\d{4})$/.exec(date),
dd = date ? (date[1] || 0) : 0,
mm = date ? (date[2] || 0) : 0,
yyyy = date ? (date[3] || 0) : 0;

/* To be sorted by yyyy => mm => dd */
return { yyyy: yyyy * 1, mm: mm * 1, dd: dd * 1 };
},
"date-eu-asc": function (a, b) {
return (a.yyyy < b.yyyy) ? -1 :
(a.yyyy > b.yyyy) ? 1 :
(a.mm < b.mm) ? -1 :
(a.mm > b.mm) ? 1 : a.dd - b.dd;
},
"date-eu-desc": function (a, b) {
return (a.yyyy > b.yyyy) ? -1 :
(a.yyyy < b.yyyy) ? 1 :
(a.mm > b.mm) ? -1 :
(a.mm < b.mm) ? 1 : b.dd - a.dd;
}
});

$.fn.dataTableExt.aTypes.unshift(
function (data) {
if (data === null) {
return null;
}
/* Full date: d(d).M(M).yyyy */
if (/^\d{1,2}\.\d{1,2}\.\d{4}$/.test(data) === true) {
return 'date-eu';
}
return null;
}
);
});
[/code]

Arkhee

+1 for the hidden span, just found it by myself after parsing a lot of complicated javascript ;)
Works for me with version 1.9.4. The span has to be at the very beginning of the cell and the tag must be the exact same for all lines (ie if your table is hand coded you should be carefull to case or things like this)

rajagopk

The solution by "deus_pater" on August 2010 worked for me. Big Thank you.

CSurfer

I currently use this code to sort my dates but it will not sort the years correctly.
I have tried many of the suggestions here and they work but something is wrong and I loose my ThemeRoller theme, so I have something wrong. Can anyone help, I am a cut and paster and have not grasped the punctuation. I need to sort the date - MM-DD-YYYY format while displaying the ThemeRoller theme smoothness.



function fnFeaturesInit ()
{
/* Not particularly modular this - but does nicely :-) */
$('ul.limit_length>li').each( function(i) {
if ( i > 10 ) {
this.style.display = 'none';
}
} );

$('ul.limit_length').append( 'Show more<\/li>' );
$('ul.limit_length li.css_link').click( function () {
$('ul.limit_length li').each( function(i) {
if ( i > 5 ) {
this.style.display = 'list-item';
}
} );
$('ul.limit_length li.css_link').css( 'display', 'none' );
} );
}

$(document).ready( function() {
fnFeaturesInit();
$('#example').dataTable( {
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"iDisplayLength": 15,
"sLengthMenu": 25,
"aLengthMenu": [[15, 25, 50, 100, -1], [15, 25, 50, 100, "All"]],
"aaSorting": [[ 4, "desc" ]]
} );

SyntaxHighlighter.config.clipboardSwf = 'media/javascript/syntax/clipboard.swf';
SyntaxHighlighter.all();
} );

chainz94

In PHP code, Im using simple function for date formating, like

function dr($gdr){
    if($gdr!="0000-00-00"){
return date('d. m. Y.', strtotime($gdr));   
    };
};

echo will be (for variable of birth date $bd)

echo '<td><span style="display:none;">'.$bd.'</span>'. dr($bd) .'</td>

In browser it appears like 27. 02. 1995.
But sorting is done for 1995-02-27, and works for me

luferogo

+1 for the span solution for 1.9.4 version. ! txs
Razor snippet:
<td>
<span style='display:none'>@Html.ValueFor(modelItem => item.Date,"{0:yyyy-MM-dd}")</span>
@Html.DisplayFor(modelItem => item.Date)
</td>

sweety90

$.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] * 60 * 24 * 366;
                var month = frDatea2[1] * 60 * 24 * 31;
                var day = frDatea2[0] * 60 * 24;
                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;
                }
                var hour1 = hour * 60;
                x = (year + month + day + hour1 + minute) * 1;
            } else {
                var x = 99999999999999999;
            }

            return x;
        },

        

        "datetime-au-asc": function (a, b) {
            return ((a < b) ? -1 : ((a > b) ? 1 : 0));
        },

        "datetime-au-desc": function (a, b) {
            return ((a < b) ? 1 : ((a > b) ? -1 : 0));
        }

 columnDefs: [
                      
                        { type: "datetime-au", "targets": 12 },
]

sorting date dd/mm/yyyy hh:mm am/pm. I have tried this code and it's working

asharsultan

Well I guess there is an easier way to get it done using the data-sort or data-order HTML 5 attribute .

See HTML Part for reference
https://datatables.net/examples/advanced_init/html5-data-attributes.html

Here is my sample code in PHP
```
<td data-order="<?php echo strtotime($date);?>">
<?php
echo date("d-m-Y H:i a" , strtotime($date));

<?php > ``` ?>