How to date sort as date instead of string

How to date sort as date instead of string

rdmrdm Posts: 166Questions: 43Answers: 3

I never noticed this until I actually needed to sort by a date. All my dates are in USA format (dd/mm/yyyy) but sort as they are strings. From what I read in various forum answers, I was under the impression that DataTables automatically recognized this as a date format. See screenshot for example.

This is all the code I have. Let's say the date column is column 5. How would I force recognition of that column as a date column so that it sorts as a date and not a string?

$(() => {
            $(".display").DataTable({
                dom: 'Bfrtip',
                scrollX:true,
                buttons: [{
                    extend: 'excelHtml5',
                    exportOptions: {
                        columns: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]
                    }
                }]
            });
        });

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 48,282Questions: 1Answers: 6,967 Site admin
    Answer ✓

    From what I read in various forum answers, I was under the impression that DataTables automatically recognized this as a date format.

    Due to how Chrome parses dates, the latest DataTables core only really supports ISO8601 format out of the box. For anything else, this is the best option.

    Allan

  • rdmrdm Posts: 166Questions: 43Answers: 3

    Just a follow up. The use of moment.js solved my problem.

    It took me a few moments to realize that I needed to place a local copy of //cdn.datatables.net/plug-ins/1.10.16/sorting/datetime-moment.js into my local folder as we don't use CDNs in our system. Once that detail was squared away, it was really easy to get the dates and time sorting as dates instead of strings.

  • flamontagneflamontagne Posts: 1Questions: 0Answers: 0

    I think a way better solution is to use the "sort" data attribute, like this :

    <td data-sort="<%= my_date.strftime("%Y%m%d%H%M%s") %>">
      <%= Display your date using any format here %>
    </td>
    

    I'm using Erb templates so code between <%= %> is server code.

  • srturgutsrturgut Posts: 1Questions: 0Answers: 0

    My solution for PHP is like this:

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

    notice that the formats of data-sort attr value $mydate and <td> inner value $newformat are different. Hope it will help somebody.

Sign In or Register to comment.