Datatable date sorting dd mm yyyy issue

Question

I am using a Jquery plugin called datatables  Its fantastic  however I cannot get the dates to sort correctly according to the dd mm yyyy format   I have looked at their support formats but none of these fixes seem to work   Can anybody here help me please

User · Answer

The simpliest way is to add a hidden timestamp before the date in every TD tag of the column, for example:

<td class="sorting_1">
    <span class="d-none">1547022615</span>09/01/2019  09:30
</td>

With the default string ordering, a timestamp would order the column the way you want and it will not be shown when rendered in the browser.

User · Answer

You can resolve this issue with php    mydate   strtotime  startdate    newformat   date  d-m-Y   mydate   echo   lt tr gt    echo     lt td data-sort      mydate     gt    newformat    lt  td gt

User · Answer

in php or js just pass an array and use orthogonal  like    var 0  0     like as u wish  30 12 2015 or something else    var 0  1    strtotime  your date variable     and  in datatable         data-table-contas pagar   dataTable        columnDefs               targets   0   data    0   render          0   sort    1

User · Answer

While there are so many answers to the question  I think data-sort works only if sorting is required in the  quot YYYYMMDD quot  and does not work while there is Hour   Minutes  The filter doesn t work properly while data-sort is used  at least I had that problem while trying in React JS  The best solution in my opinion is to use data-order as the value can be provided dynamically for sorting purpose and format can be different while displaying  The solution is robust and works for any date formats including  quot DD MM YYYY HH M quot   For example   lt td data-order  obj plainDateTime  gt  this formattedDisplayDate obj plainDateTime    lt  td gt   I found this solution from here - How do I sort by a hidden column in DataTables

User · Answer

use this snippet     document  ready function        fn dataTable moment   function   format  locale         var types     fn dataTable ext type          Add type detection     types detect unshift  function   d             return moment  d  format  locale  true   isValid                  moment-  format               null                   Add sorting method - use an integer for the sorting     types order   moment-  format  -pre      function   d             return moment  d  format  locale  true   unix                 fn dataTable moment  DD MM YYYY         example   DataTable          the moment js works well for all date and time formats  add this snipper before you initialize the datatable like i ve done earlier   Also remember to load the http   momentjs com

User · Answer

Anyone struggling with UTC formats or others can read this  Suppose you have date in this format     Tue Oct 15 2019 08 41 35 GMT 0000  UTC    First we can convert it to millisecond using moment   For Example  in my case i was using HandleBar js  So i created a Helper function to make it simpler  hbs registerHelper  dateformat   function  datetime    return moment datetime  valueOf         or else    just convert it this way  moment  Tue Oct 15 2019 08 41 35 GMT 0000  UTC    valueOf      once done just pass these values to your table  Now the trick here is to pass them both and hide the one in milliseconds and show the one in UTC format   lt td  gt   lt span class  hideThisDate  gt   DATA IN MILLISECONDS   lt  span gt     YOUR DATE IN NORMAL FORMAT   lt  td gt    Now just simply hide the one in milliseconds through CSS   hideThisDate    display none       And you should be good to go

User · Answer

Zaheer Ahmed  solution works fine if you have to deal with already uk formated date    I had an issue with this solution because I had to manage US formated date    I figured it out with this tiny change     function parseDate a        var ukDatea   a split           return  ukDatea 2    ukDatea 1    ukDatea 0     1     jQuery extend  jQuery fn dataTableExt oSort         date-uk-pre   function   a             return parseDate a                date-uk-asc   function   a  b             a   parseDate a           b   parseDate b           return   a  lt  b    -1     a  gt  b    1   0                 date-uk-desc   function   a  b             a   parseDate a           b   parseDate b           return   a  lt  b    1     a  gt  b    -1   0                Followed by your  aoColumns  definition

User · Answer

I wanted to point out that when using data from the server via Ajax  the solution is super simple  but may not be immediately obvious   When returning the sort order array  Datatables will send  in the   POST  a 2 element array that would be equivalent to     POST  order   0   array  column   gt  SortColumnName    dir   gt  asc        2nd element is either  asc  or  desc    Therefore  you may display the date in any format you want  just have your server return the sorting criteria based only upon the sortColumnName   For example  in PHP  with MySQL   I use the following    if  isset   POST  order                 switch    POST  order   0   column                     case 0    sort by Primary Key                      order    pkItemid                       break                 case 1    Sort by reference number                      order    refNo                       break                 case 2   Date Started                      order    dOpen                       break                 default                        order    pkItemid                          orderdir      POST  order   0   dir        desc      desc     asc            Note  that since nothing from the   POST is passed to  order or  orderdir  no cross-script attack is possible   Now  just append to a MySQL query    sql   SELECT pkItemid  refNo  DATE FORMAT dOpen   b  e   Y   AS dateStarted        FROM tblReference         ORDER BY  order  orderdir      run the query  and return just the dateStarted value to Datatables in json

User · Answer

To the column you want ordering keep  sType    date-uk  for example - data    OrderDate    sType    date-uk  After the completion of Datatable script in ajax keep the below code   jQuery extend jQuery fn dataTableExt oSort                 date-uk-pre   function  a                    var ukDatea   a split                       return  ukDatea 2    ukDatea 1    ukDatea 0     1                               date-uk-asc   function  a  b                    return   a  lt  b    -1     a  gt  b    1   0                                 date-uk-desc   function  a  b                    return   a  lt  b    1     a  gt  b    -1   0                                Then You will get date as 22-10-2018 in this format

User · Answer

This solution is completely wrong  You can t convert a date to a number just adding each component of the date  If you try this logic for example with the following dates  you ll see it won t match correctly     20 01 2014   2035 15 02 2014   2031  Witch date comes first  ascending  20 of january  Not according to this logic  P  The correct way of doing the parsedate method is to convert the string to a valid datetime  and them use the getTime function to properly order the table   var day   a split      0  var month   a split      1  var year   a split      2   var date   new Date month         day         year  return date getTime

User · Answer

If you don t want to use momentum js or any other date formating  you can prepend a date format in milliseconds in the date value so that the sort will read according to it s millisecond  And hide the milliseconds date format   Sample code    var date   new Date    var millisecond   Date parse date     HTML   lt td gt   lt span style  display  none   gt     millisecond     lt  span gt     date    lt  td gt    That s it

User · Answer

Date Sort - with a hidden element Convert the date to the format YYYYMMDD and prepend to the actual value  DD MM YYYY  in the  lt td gt   wrap it in an element  set style display none  to the elements  Now the date sort will work as a normal sort  The same can be applied to date-time sort  HTML  lt table id  quot data-table quot  gt      lt tr gt        lt td gt  lt span gt YYYYMMDD lt  span gt DD MM YYYY lt  td gt      lt  tr gt   lt  table gt   CSS  data-table span       display none

User · Answer

I used in the rest call    Date Variable is  Created     var call     ajax               url       api Web Lists GetByTitle  NewUser   items  filter  Created 20ge 20datetime   FromDate     20and 20 Created 20le 20datetime   ToDate    and Title eq    epf      amp  top 5000               type   GET               dataType   json               headers                    Accept   application json odata verbose                                    call done function  data textStatus  jqXHR               example   dataTable                bDestroy   true               bProcessing   true               aaData   data d results               aLengthMenu                    50 100                 50 100                              dom   Bfrtip               buttons                     copy    csv    excel                               aoColumnDefs       bVisible   false                   aoColumns                        mData    ID                        mData    Title                        mData    EmployeeName                        mData    Department1                        mData    ServicingAt                        mData    TestField                         mData    BranchCode                           mData    Created    render   function  data  type  row            data   moment data  format  DD MMM YYYY            return data

User · Answer

I know this is an old question and answers are old too  Recently I came across a simple and clean way of sorting dates  It can be done by HTML5 data-order attribute to  lt td gt  element   Here s what I have done in my PHP    lt  php  newdate   date  d M Y    myDateTime      Format in which I want to display  dateOrder   date  Y-m-d    myDateTime      Sort Order   gt    lt td data-order   lt  php echo  dateOrder    gt    gt   lt  php echo  newdate    gt   lt  td gt

User · Answer

Another solution  https   datatables net blog 2014-12-18  with 2 JavaScript libraries    cdnjs cloudflare com ajax libs moment js 2 8 4 moment min js cdn datatables net plug-ins 1 10 15 sorting datetime-moment js   then only this      document  ready function          fn dataTable moment   DD MM YYYY            example   DataTable

User · Answer

Update 2020  HTML Solution Since HTML 5 is so much developed and almost all major browser supporting it  So now a much cleaner approach is to use HTML5 data attributes  maxx777 provided a PHP solution I am using the simple HTML   For non-numeric data as in our scenario  we can use data-sort or data-order attribute and assign a sortable value to it  HTML  lt td data-sort  YYYYMMDD  gt DD MM YYYY lt  td gt   Here is working HTML solution jQuery Solution Here is working jQuery solution  jQuery extend  jQuery fn dataTableExt oSort     quot date-uk-pre quot   function   a         var ukDatea   a split           return  ukDatea 2    ukDatea 1    ukDatea 0     1       quot date-uk-asc quot   function   a  b         return   a  lt  b    -1     a  gt  b    1   0         quot date-uk-desc quot   function   a  b         return   a  lt  b    1     a  gt  b    -1   0              Add the above code to script and set the specific column with Date values with     quot sType quot    quot date-uk quot    and others as null  see below    document  ready function             example   dataTable             quot aoColumns quot                 null              null              null              null                 quot sType quot    quot date-uk quot                 null

User · Answer

Try this    aoColumns             null          null          null          null            sType    date          sType    date  TO SPECIFY SORTING IS APPLICABLE ON DATE         null

User · Answer

Use the data-order attribute on the  lt td gt  tag like so  Ruby Example         lt td data order   lt   rentroll decorate date  gt   gt  lt   rentroll decorate date  gt  lt  td gt    Your decorator function here would be       def date     object date amp  strftime   d  m  Y       end

User · Answer

Problem source is datetime format   Wrong samples   MM-dd-yyyy H mm   MM-dd-yyyy   Correct sample   MM-dd-yyyy HH mm

User · Answer

I tried this and worked for me   https   github com sedovsek DataTables-EU-date-Plug-In  I used the format mode  ToString  dd MM yyyy    then in my jQuery Datatable works fine   jQ below  oTable       grid   dataTable        sPaginationType    full numbers        aoColumns                sType    eu date             null                 The column you have dates  you should define with the sType like the code above

User · Answer

Try this plugin    As stated here you need to include Moment js and the datatable-moment plugin  then just declare the date format you are using  The plugin will autodetect your date columns and sort it like it should be  For moment js format explanations  check here   Example     document  ready function           fn dataTable moment  DD MM YYYY HH mm            example   DataTable

User · Answer

As I found the easiest way for sorting in this case is by adding  aaSorting  option in the JS    For example     document  ready function             contacts-table   dataTable            aaSorting    0   desc         The problem here is that this example will sort entries from 1-st column like STRING but not like dates  If source code allows you to change date format from dd mm yyyy to yyyy mm dd  aaSorting  will work properly for you

User · Answer

I too got same problem   I was using span with in td like 03 21 2017  by doing this  datatable treated this as string and sorting did not work   I removed span inside td  and it got fixed  like   03 21 2017

User · Answer

If you get your dates from a database and do a for loop for each row and append it to a string to use in javascript to automagically populate datatables  it will need to look like this  Note that when using the hidden span trick  you need to account for the single digit numbers of the date like if its the 6th hour  you need to add a zero before it otherwise the span trick doesn t work in the sorting    Example of code    DateTime getDate2   Convert ToDateTime row  date      var hour   getDate2 Hour ToString     if  hour Length    1      hour    0    hour      var minutes   getDate2 Minute ToString     if  minutes Length    1      minutes    0    minutes      var year   getDate2 Year ToString     var month   getDate2 Month ToString     if  month Length    1      month    0    month      var day   getDate2 Day ToString     if  day Length    1      day    0    day      var dateForSorting   year   month   day   hour   minutes    dataFromDatabase Append   lt span style  u0022display none  u0022 gt     dateForSorting     lt  span gt

User · Answer

This way it worked for me    lt td data-order   item CreatedOn ToString  MMddyyyyHHmmss    gt       item CreatedOn ToString  dd-MM-yyyy hh mm tt    lt  td gt    This date format in data-order attribute should be in this format which is being supported by DataTable

User · Answer

The most Easy way to Sort out this problem  Just modify your design little bit like this    x000D   x000D    Add this data order attribute to td x000D   lt td data-order   item CreatedOn ToUnixTimeStamp    gt  x000D                                       item CreatedOn x000D                                   lt  td gt  x000D                                   x000D                                   x000D                  x000D                 Add create this Date Time helper function x000D      region Region  x000D   public static long ToUnixTimeStamp this DateTime dateTime    x000D   TimeSpan timeSpan    dateTime - new DateTime 1970  1  1  0  0  0    x000D       return  long timeSpan TotalSeconds  x000D          x000D        endregion x000D   x000D   x000D

User · Answer

What seems to work for me was   push the full datetime object fetched with a select query from my db in a dataset wich will be draw by datatable    format  2018-01-05 08 45 56   then          Table   DataTable           data  dataset          deferRender  200          destroy  true          scrollY  false          scrollCollapse  true          scroller  true           order     2   desc              columnDefs                                    targets   2                   createdCell    function  td  cellData  rowData  row  col                                                var datestamp   new Date cellData                         td  html datestamp getUTCDate      -     datestamp getMonth   1     -    datestamp getFullYear                                                         initComplete   function settings  json                    fn dataTable tables true   DataTable                    columns adjust                                       Rows get sorted right   then I get a html I want in the row

[jquery] Datatable date sorting dd/mm/yyyy issue

Examples related to jquery

Examples related to datatables

Examples related to date-sorting