Need help for sorting date with dd/mm/yyyy format - Page 2

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

2»

Replies

  • virgilvirgil Posts: 12Questions: 0Answers: 0
    edited June 2013
    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.
  • virgilvirgil Posts: 12Questions: 0Answers: 0
    It did work without pre and just doing everything in date-ro-desc and date-ro-asc. Thanks
  • dylanmacdylanmac Posts: 49Questions: 7Answers: 1
    Anyone have solutions for formatting dates the American way: mm/dd/yyyy? I don't see any plugins that support this.
  • roadyroady Posts: 1Questions: 0Answers: 0
    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]
  • contohsuratindonesiacontohsuratindonesia Posts: 1Questions: 0Answers: 0
    the easiest way is use: [date in yyyy-mm-dd format] [date in your format]. Work for me :D
  • mfittkomfittko Posts: 1Questions: 0Answers: 0
    +1 on the hidden span solution. no js hacking needed here!
  • allanallan Posts: 49,705Questions: 1Answers: 7,336 Site admin
    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
  • sparkplantsparkplant Posts: 1Questions: 0Answers: 0
    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
  • allanallan Posts: 49,705Questions: 1Answers: 7,336 Site admin
    Nice one - thanks for sharing this with us!

    Allan
  • ChookoChooko Posts: 1Questions: 0Answers: 0
    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]
  • ArkheeArkhee Posts: 1Questions: 0Answers: 0
    +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)
  • rajagopkrajagopk Posts: 2Questions: 0Answers: 0
    The solution by "deus_pater" on August 2010 worked for me. Big Thank you.
  • CSurferCSurfer Posts: 2Questions: 0Answers: 0
    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();
    } );
  • chainz94chainz94 Posts: 1Questions: 0Answers: 0
    edited September 2014

    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

  • luferogoluferogo Posts: 1Questions: 0Answers: 0

    +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>

  • sweety90sweety90 Posts: 1Questions: 0Answers: 0
    edited February 2015
    $.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

  • asharsultanasharsultan Posts: 1Questions: 0Answers: 0

    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));
    ?>
    </td>
    
This discussion has been closed.