3

I have a table with multiple columns. 1 column contains date in format dd.mm.yyyy (example: 26.05.2021). I'm trying to achieve a default sorting by date.

My table looks like this:

<table id="myTable" class="table table-striped table-hover" style="width:100%">
            <thead>
                <tr>
                    <th>Title</th>
                    <th>Date</th>
                    <th>Time</th>
                    <th>Notes</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Some Text.</td>
                    <td>25.06.2021</td> <!-- This is the date column I want to sort by -->
                    <td>15:10</td>
                    <td>Some Text 2</td>
                </tr>
                <tr>
                    <td>Some Text</td>
                    <td>22.07.2020</td> <!-- This is the date column I want to sort by -->
                    <td>16:00</td>
                    <td>Some Text XYZ</td>
                </tr>
            </tbody>
            <tfoot>
                <tr>
                    <th>Title</th>
                    <th>Date</th>
                    <th>Time</th>
                    <th>Notes</th>
                </tr>
            </tfoot>
        </table>

So far, I have this JS at the end of my <body> in my HTML file:

<script type="text/javascript" href="https://cdn.datatables.net/plug-ins/1.10.25/sorting/date-eu.js"></script>
<script type="text/javascript">
        $('#myTable').DataTable({
            "language": {
                "url": "https://cdn.datatables.net/plug-ins/1.10.18/i18n/Slovak.json"
            },
            columnDefs: [{
                type: 'date-eu',
                targets: 1
            }],
            "order": [
                [1, "desc"],
                [2, "desc"]
            ],
            "pagingType": "first_last_numbers"
        });
</script>

The issue is, that this does not order the table correctly. It seems to be ordering only by the day (ignoring month and year), not by the whole date.

Any ideas how to proceed? I have tried all the available answers I was able to find here and also on the DataTables forums, but there weren't any answers which would fix my issue...

Thank you

neisor
  • 384
  • 4
  • 15

1 Answers1

6

Because you have two different date/time formats in your table (one for the column 2 date and one for the column 3 time), I recommend using the ultimate date/time sorting plug-in.

You need these extra resources in the page header:

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.26.0/moment.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.10.25/sorting/datetime-moment.js"></script>

Then, in the body script, you can define the two formats you need:

$.fn.dataTable.moment( 'DD.MM.YYYY' );
$.fn.dataTable.moment( 'HH:mm' );

Formatting options for those two strings are documented here as part of the moment.js library.

DataTables takes care of the rest.

It looks through the list of date/time formats you have provided and automatically fits the correct format to the relevant column data. It then uses that format to ensure the data is sorted chronologically, while leaving the display format unchanged.

A demo:

<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">

  <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.26.0/moment.min.js"></script>
  <script src="https://cdn.datatables.net/plug-ins/1.10.25/sorting/datetime-moment.js"></script>

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                    <th>Title</th>
                    <th>Date</th>
                    <th>Time</th>
                    <th>Notes</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Some Text A</td>
                    <td>21.06.2021</td>
                    <td>15:10</td>
                    <td>Some Text 2</td>
                </tr>
                <tr>
                    <td>Some Text B</td>
                    <td>22.07.2020</td>
                    <td>16:00</td>
                    <td>Some Text XYZ</td>
                </tr>
                <tr>
                    <td>Some Text C</td>
                    <td>22.07.2020</td>
                    <td>15:59</td>
                    <td>Some Text XYZ</td>
                </tr>
            </tbody>
            <tfoot>
                <tr>
                    <th>Title</th>
                    <th>Date</th>
                    <th>Time</th>
                    <th>Notes</th>
                </tr>
            </tfoot>
        </table>

</div>

<script type="text/javascript">

$(document).ready(function() {

  $.fn.dataTable.moment( 'DD.MM.YYYY' );
  $.fn.dataTable.moment( 'HH:mm' );

  $('#example').DataTable( {
    order: [
      [1, "desc"],
      [2, "desc"]
    ],
  } );

} );

</script>

</body>
</html>
andrewJames
  • 19,570
  • 8
  • 19
  • 51
  • Thank you for your reply, but when I add a new table row, it automatically stops working: https://jsfiddle.net/3am8502b/1/ Having said that and provided the jsfiddle, this solution did not somehow work for me :-( – neisor Jun 28 '21 at 17:19
  • I opened your fiddle via the link you provided and the data looks to be sorted correctly to me: The row containing "Some Text D" is sorted to the bottom of the table, because it has the earliest date ("22.06.2020"). Can you clarify what you are seeing that appears to be incorrect? – andrewJames Jun 28 '21 at 17:42
  • Well, it sorts it in this manner: 21.06., 22.07., 22.07. and the last one is 22.06. So this is not correct. It should be like this: 21.06., 22.06., 22.07., 22.07. – neisor Jun 28 '21 at 18:01
  • The sort order you want to see does not match the data you have provided. Why would your new row of data appear second when it is the oldest date in the list? – andrewJames Jun 28 '21 at 18:24
  • It's not the oldest in the list. The oldest is 21.06. and the latest is 22.07. So, 22.06. should be in the middle between them, but it is not in the middle in the jsfiddle example – neisor Jun 28 '21 at 19:33
  • Are we looking at two different things? I am looking at the data in your question and the data in your Fiddle. Where are you looking? Your question asks to sort by date. The title of your question states that your dates are "dd.mm.yyyy". The body of your question also clarifies that you want to sort by "the whole date" including "month and year". Row D (the one you added) has the following date: `22.06.2020`. Which of your other rows has a date older than that one? I cannot find any date older than that in your data. – andrewJames Jun 28 '21 at 19:45
  • 1
    Ugh, sorry, you are right. I misread my own jsfiddle's data/row I put in. Yep, it is working there correctly. Thank you very much! Your solution works. – neisor Jun 28 '21 at 20:14
  • We had a time traveling adventure there - but I am glad it works - thank you. – andrewJames Jun 28 '21 at 20:17
  • Indeed. Thank you very much for your time and effort to help me! Much appreciated! :-) – neisor Jun 29 '21 at 07:53