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

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

jd-webdesignjd-webdesign Posts: 15Questions: 0Answers: 0
edited August 2010 in General
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
«1

Replies

  • deus_paterdeus_pater Posts: 3Questions: 0Answers: 0
    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-webdesignjd-webdesign Posts: 15Questions: 0Answers: 0
    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_paterdeus_pater Posts: 3Questions: 0Answers: 0
    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-webdesignjd-webdesign Posts: 15Questions: 0Answers: 0
    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-webdesignjd-webdesign Posts: 15Questions: 0Answers: 0
    edited August 2010
    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_paterdeus_pater Posts: 3Questions: 0Answers: 0
    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-webdesignjd-webdesign Posts: 15Questions: 0Answers: 0
    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:)
  • kkudikkudi Posts: 77Questions: 0Answers: 0
    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.
  • PaoloValladolidPaoloValladolid Posts: 35Questions: 0Answers: 0
    datejs.com is noticeably slower than the default Date.parse. OTOH, it gets the dates sorted correctly.
  • ComboFusionComboFusion Posts: 1Questions: 0Answers: 0
    I would like to add that the plugin mentioned by jd-webdesign DOES NOT sort date-time properly.
  • melankemelanke Posts: 1Questions: 0Answers: 0
    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]
  • gsgill76gsgill76 Posts: 4Questions: 0Answers: 0
    I had same issue of sorting (US-Style DateTime Field)
    [quote]deus_pater[/quote] you solve my issue.... Thanks a-lot
  • AshishusAshishus Posts: 1Questions: 0Answers: 0
    deus_pater


    Please explain me in details , how to use hidden column and sort the corresponding date column .
  • DannyDanny Posts: 2Questions: 0Answers: 0
    Thank's melanke. It run ok, but I have a column with dontSort and now it doesn't work.
  • DannyDanny Posts: 2Questions: 0Answers: 0
    edited January 2012
    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]
  • GangadharGangadhar Posts: 3Questions: 0Answers: 0
    edited February 2012
    Thanks "melanke" for your code.
  • jheaddonjheaddon Posts: 2Questions: 0Answers: 0
    edited March 2012
    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!
  • jheaddonjheaddon Posts: 2Questions: 0Answers: 0
    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;
    };
  • annoyingmouseannoyingmouse Posts: 2Questions: 0Answers: 1
    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
  • icsnerdicsicsnerdics Posts: 1Questions: 0Answers: 0
    edited August 2012
    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
  • surajitdeysurajitdey Posts: 1Questions: 0Answers: 0
    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
  • axayacaxayac Posts: 1Questions: 0Answers: 0
    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
  • khushalkhushal Posts: 1Questions: 0Answers: 0
    edited December 2012
    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]
  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Excellent - thank you for posting that :-)

    Allan
  • NPradhipNPradhip Posts: 1Questions: 0Answers: 0
    edited January 2013
    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;
    }
    } );
  • caseymarcallencaseymarcallen Posts: 1Questions: 0Answers: 0
    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]
  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Very nice - thanks for sharing :-)

    Allan
  • miller345miller345 Posts: 1Questions: 0Answers: 0
    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]
  • FredDaggFredDagg Posts: 1Questions: 0Answers: 0
    edited April 2013
    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]
  • paulofer85paulofer85 Posts: 1Questions: 0Answers: 0
    edited April 2013
    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]
This discussion has been closed.