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?

have you read this : http://www.datatables.net/release-datatables/examples/plug-ins/sorting_plugin.html — diEcho, Aug 17 '12 at 09:34
I've had a look at it but dont wuite understand how to apply it to dates. I have substituted forward slashes for dashes but am not sure about the rest. It says it checks for one decimal place, how would you make that check for two dashes? — jaget, Aug 17 '12 at 09:42
You didn't mention the method for loading data, so I have added an *AJAX* solution to the mix. I find most of my tables start as in-browser data, but eventually all migrate to AJAX anyway. — Sablefoste, Aug 14 '15 at 13:42

score 150 · Accepted Answer · edited Jun 20 '20 at 09:12

150

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

<td data-sort='YYYYMMDD'>DD/MM/YYYY</td>

Here is working HTML solution

jQuery Solution

Here is working jQuery solution.

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 < b) ? -1 : ((a > b) ? 1 : 0));
},

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

Add the above code to script and set the specific column with Date values with { "sType": "date-uk" } and others as null, see below:

$(document).ready(function() {
    $('#example').dataTable( {
        "aoColumns": [
            null,
            null,
            null,
            null,
            { "sType": "date-uk" },
            null
        ]
    });
    });

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 17 '12 at 09:42

Zaheer Ahmed

26,435
11
70
105

Not sure if I have got the right link but when I put 24/4/2012 in it doesnt work. http://live.datatables.net/ipogen/edit#preview – jaget Aug 17 '12 at 09:45
1

You got the correct answer quicker than me. I was going to use this http://live.datatables.net/abarot/edit#javascript,html but yours is much better code, and made in less time. Thanks very much for your help. Still not sure how the smaller functions get used, but at least I get how they work. Thank you very much for your help. – jaget Aug 17 '12 at 10:19
1

i don't know what im missing, but this does not sort the dates correctly, not even in the edited link. – Thousand Apr 07 '14 at 11:51
agreed. this only sorts by the day and not by month or year. – lukeocom Jul 23 '14 at 03:57
Test the link, but it is not sorting dates as it should. – Fede May 07 '15 at 13:32
1

@Fede actually the link in post was with `/edit` which lately get updated, I have corrected the link. Please note it just sort dd/mm/yyyy format dates. – Zaheer Ahmed May 07 '15 at 18:20
2

If you want to sort the dates as strings and not as numbers you have to remove the " * 1" at the end of the "date-uk-pre" function, which converts the returned date into number. With this change, plays like charm! :) – Nicki Voutou Dec 05 '16 at 11:22
2

This is an elegant fix – Athul Raj Feb 28 '18 at 11:08
2

css solution is better, tjhanks – Alberto Acuña May 17 '18 at 07:45
Somehow sType solution is not working for me but CSS solution works ! Thanks ! – tom Aug 27 '18 at 02:17
The CSS solution is awesome! Thanks! – BJ Dela Cruz Oct 04 '18 at 00:23
Quick fix for the sorting issue. ! – Giri Annamalai M Sep 25 '19 at 10:38
This is my solution – EduardoUstarez Apr 09 '20 at 18:23

score 100 · Answer 2 · edited Jun 20 '20 at 09:12

100

Date Sort - with a hidden element

Convert the date to the format YYYYMMDD and prepend to the actual value (DD/MM/YYYY) in the <td>, 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

<table id="data-table">
   <tr>
     <td><span>YYYYMMDD</span>DD/MM/YYYY</td>
   </tr>
</table>

CSS

#data-table span {
    display:none; 
}

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 20 '14 at 13:22

Anulal S

5,824
5
21
31

3

OMG nothing worked except this. This should be the code you will need if you use rails (idea is just the model name that i used): ` ` – matias salgado Feb 15 '17 at 15:54
8

Unfortunatety when you use a datatables export pdf plugin, it take all you `HTML` tags (including the hideen `HTML` tags). The result is data duplicate in your file exported. It export the data inside the `` too – Are0215 Feb 13 '18 at 20:34
@AnulalS sorry I was away from the keyboard and just saw your comments. This was a very old post, I have updated my answer. Thanks for pointing the correction. – Zaheer Ahmed Nov 14 '19 at 11:22
@Are0215 you can customize the generated content to remove the duplicate data from the span: buttons: [{extend: 'pdfHtml5', customize: function (doc) {doc.content[0].table.body.forEach(function (row) { row[5].text = row[5].text.split('').pop();});}} – Alexis Delahaye Jan 06 '20 at 10:12
It works for me but I don't understand the logic behind it. Can you explain ? – David Coder Jul 07 '20 at 10:16
@DavidCoder - 'YYYYMMDD' format is a sorted number by default, for example, today 20200707 is greater than yesterday 20200706 – Anulal S Jul 07 '20 at 10:28

maxx777 · Answer 3 · 2017-01-28T17:18:25.140

40

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 <td> element.

Here's what I have done in my PHP:

<?php
$newdate = date('d M Y', $myDateTime); // Format in which I want to display
$dateOrder = date('Y-m-d', $myDateTime); // Sort Order
?>

<td data-order="<?php echo $dateOrder; ?>" >
<?php echo $newdate; ?>
</td>

edited Jan 28 '17 at 17:18

answered Dec 02 '15 at 08:46

maxx777

1,264
1
19
37

4

The documentation for this feature can be found here: https://datatables.net/release-datatables/examples/advanced_init/html5-data-attributes.html – mitchdav Jul 04 '18 at 02:42

score 10 · Answer 4 · edited Dec 21 '16 at 09:17

10

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();
});

edited Dec 21 '16 at 09:17

ThEBiShOp

434
6
21

answered Nov 15 '15 at 23:40

mineroot

1,487
15
22

what if there is time included? – Eli Mar 29 '16 at 11:00
@Eli have you tried `$.fn.dataTable.moment('DD.MM.YYYY HH.mm.ss');`? Where `HH.mm.ss` is your time format. [More](http://momentjs.com/docs/#/displaying/format/) – mineroot Mar 29 '16 at 11:14
1

$.fn.dataTable.moment('YYYY.MM.DD HH.mm.ss'); I used this but its not working – Eli Mar 29 '16 at 11:27
I have a Date Created column in my table but its not being sorted – Eli Mar 29 '16 at 11:29
Is your datetime like `2016.03.29 14.33.08`? Look at [example section](https://datatables.net/blog/2014-12-18). It all depends of your format. – mineroot Mar 29 '16 at 11:35
2016-03-06 12:19:58, it looks like this in my database and the way I showed it on my table – Eli Mar 29 '16 at 11:37
@Eli Then you have to `$.fn.dataTable.moment('YYYY-MM-DD HH:mm:ss');` – mineroot Mar 29 '16 at 11:45
, I have included this script. – Eli Mar 29 '16 at 11:48
Date Created {{ $val->created_at }} – Eli Mar 29 '16 at 11:50
what is the approach for multiple tables with different date formats? – ibubi Oct 16 '19 at 05:32

score 10 · Answer 5 · answered Jun 17 '19 at 09:40

10

This way it worked for me.

<td data-order="@item.CreatedOn.ToString("MMddyyyyHHmmss")">
    @item.CreatedOn.ToString("dd-MM-yyyy hh:mm tt")
</td>

This date format in data-order attribute should be in this format which is being supported by DataTable.

answered Jun 17 '19 at 09:40

Reyan Chougle

3,853
1
25
40

1

I think this should be considered as correct solution. while data-sort serves the purpose, fitler does not work properly in the data table. Also this will not help if you want to sort "YYYYMMDD" with "HH MM". I found similar solution from - https://stackoverflow.com/questions/38079208/how-do-i-sort-by-a-hidden-column-in-datatables/38079753 and tried implementation and that solves the mystry of sorting dates of any format - For Example: {this.formatDisplayDate(file.formatedPrintTime) } – Maulik Kayastha Jun 25 '20 at 05:53

score 4 · Answer 6 · edited Jun 21 '18 at 06:35

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

<td>'<span style="display: none;">' + millisecond + "</span>" + date + </td>

That's it.

score 2 · Answer 7 · edited Nov 25 '19 at 13:10

2

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(); 
});

edited Nov 25 '19 at 13:10

Zaheer Ahmed

26,435
11
70
105

answered Jun 12 '17 at 12:57

Patrikoko

469
5
4

score 2 · Answer 8 · answered Apr 17 '19 at 05:46

2

You can resolve this issue with php.

$mydate = strtotime($startdate);
$newformat = date('d-m-Y',$mydate);
echo '<tr>';
echo '  <td data-sort="'. $mydate .'">'.$newformat .'</td>';

answered Apr 17 '19 at 05:46

augustine jenin

334
2
10

Marcelo · Answer 9 · 2014-06-27T13:21:09.603

1

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.

edited Jun 27 '14 at 13:21

answered Jun 25 '14 at 20:10

Marcelo

41
3

1

Please elaborate on why it worked instead of just posting code and a link. – eddie_cat Jun 25 '14 at 20:30

score 1 · Answer 10 · answered Aug 28 '15 at 13:51

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]}}
    ]
});

score 1 · Answer 11 · edited Jun 28 '20 at 13:05

While there are so many answers to the question, I think data-sort works only if sorting is required in the "YYYYMMDD" 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 "DD/MM/YYYY HH:M".

For example:

<td data-order={obj.plainDateTime}>{this.formattedDisplayDate(obj.plainDateTime) }</td>

I found this solution from here - How do I sort by a hidden column in DataTables?

score 0 · Answer 12 · answered Jun 21 '13 at 09:38

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 < b) ? -1 : ((a > b) ? 1 : 0));
    },

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

Followed by your "aoColumns" definition.

where should I call parseDate? – Burak Karakuş Aug 11 '14 at 14:18 — Burak Karakuş, Aug 11 '14 at 14:18

Stormhashe · Answer 13 · 2014-11-04T12:26:21.040

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()

Sablefoste · Answer 14 · 2015-08-14T13:40:13.910

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'=>'SortColumnName', 'dir'=>'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.

Should be noted that server-side processing must be turned on for this to work. — Apps-n-Add-Ons, Aug 29 '18 at 16:44

score 0 · Answer 15 · answered Aug 24 '16 at 06:14

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/

score 0 · Answer 16 · answered Apr 03 '17 at 14:22

0

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

answered Apr 03 '17 at 14:22

Srinivas Ch

46
6

score 0 · Answer 17 · answered Jun 28 '17 at 09:47

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+"' )&$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;
    }

score 0 · Answer 18 · answered Aug 01 '17 at 11:00

The most Easy way to Sort out this problem

Just modify your design little bit like this

//Add this data order attribute to td
<td data-order="@item.CreatedOn.ToUnixTimeStamp()">
                                    @item.CreatedOn
                                </td>
                                
                                
               
               Add create this Date Time helper function
// #region Region 
 public static long ToUnixTimeStamp(this DateTime dateTime) {
 TimeSpan timeSpan = (dateTime - new DateTime(1970, 1, 1, 0, 0, 0));
     return (long)timeSpan.TotalSeconds;
     } 
     #endregion

score 0 · Answer 19 · answered Jan 16 '18 at 02:52

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

score 0 · Answer 20 · answered Feb 22 '18 at 14:41

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.

aabiro · Answer 21 · 2018-07-14T00:47:40.797

0

Use the data-order attribute on the <td> tag like so (Ruby Example):

    <td data order='<%=rentroll.decorate.date%>'><%=rentroll.decorate.date%></td>

Your decorator function here would be:

    def date
    object.date&.strftime("%d/%m/%Y")
    end

edited Jul 14 '18 at 00:47

answered Jul 13 '18 at 13:36

aabiro

1,744
1
17
27

score 0 · Answer 22 · answered Aug 10 '18 at 13:11

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("<span style=\u0022display:none;\u0022>" + dateForSorting +
 </span>");

score 0 · Answer 23 · edited Oct 22 '18 at 09:54

0

Try this:

"aoColumns": [
        null,
        null,
        null,
        null,
        {"sType": "date"},  //  "sType": "date" TO SPECIFY SORTING IS APPLICABLE ON DATE
        null
      ]

edited Oct 22 '18 at 09:54

Suraj Rao

28,186
10
88
94

answered Oct 22 '18 at 09:54

Nikhil Gyan

632
8
16

score 0 · Answer 24 · answered Oct 23 '18 at 07:11

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 < b) ? -1 : ((a > b) ? 1 : 0));
            },

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

Then You will get date as 22-10-2018 in this format

Special in the sense?? I thought the above comments are not so brief, so I just kept it straight and short,I posted this answer after testing that code in my machine — vidya, Oct 23 '18 at 07:42

score 0 · Answer 25 · answered Nov 08 '18 at 10:50

0

Problem source is datetime format.

Wrong samples: "MM-dd-yyyy H:mm","MM-dd-yyyy"

Correct sample: "MM-dd-yyyy HH:mm"

answered Nov 08 '18 at 10:50

Serdar KUŞ

365
3
14

score 0 · Answer 26 · answered Jan 10 '19 at 08:58

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.

score 0 · Answer 27 · answered Oct 15 '19 at 11:00

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

<td >
<span class="hideThisDate">{{DATA IN MILLISECONDS}}</span> 
{{YOUR DATE IN NORMAL FORMAT}}</td>

Now just simply hide the one in milliseconds through CSS

.hideThisDate {
 display:none;
 }

And you should be good to go!

Karobwe · Answer 28 · 2021-03-27T08:23:35.867

I got the same issue while working with Doctrine. My datas was correctly sorted from the database with orderBy('p.updatedAt', 'DESC'), but when DataTable process them the final result was completly different.

The esiest way I found to resolve this is to add a hidden column timestamp in my table then order by this column when ordering by date. Full functional example here.

<table>
  <thead>
    <th>Some column<th>
    <th>Visible date<th>
    <th>Timestamp<th>
  </thead>

  <tbody>
    <td>Example with Twig</td>
    <td>{{ product.updatedAt ? time_diff(product.updatedAt) : '' }}</td>
    <td>{{ product.updatedAt ? product.updatedAt.getTimestamp() : '' }}</td>
  </tbody>
</table>

$(document).ready(function() {
  let dateColumn = 1;
  let timestampColumn = 2;
  let currentColumn = timestampColumn;
  let currentDirection = "desc";

  let table = $("#dataTable").DataTable({
    "order": [
        [ timestampColumn, "desc" ],
        // If you want to keep the default order from database
        // just set "order": [] so DataTable wont define other order
    ],
    "columnDefs": [
      {
        "targets": [ timestampColumn ],
        "visible": false, // Hide the timestamp column
      },
    ]
  });

  /**
   * @var e: Events
   * @var settings: DataTable settings
   * @var ordArr: Current order used by DataTable
   *      example: [{"src":8,"col":8,"dir":"asc","index":0,"type":"string"}]
   */
  table.on("order.dt", function (e, settings, ordArr) {
    currentColumn = ordArr[0].col;
    currentDirection = ordArr[0].dir;

    if(currentColumn === dateColumn) {
      table.order([timestampColumn, currentDirection]).draw();

      // Without this line you'll get an unexpected behaviour
      table.order([dateColumn, currentDirection]); 
    }
  })
});

Datatable date sorting dd/mm/yyyy issue

28 Answers28

Update 2020: HTML Solution

jQuery Solution

Date Sort - with a hidden element

Linked

Related