How to date sort as date instead of string

How to date sort as date instead of string

rdmrdm Posts: 192Questions: 54Answers: 4

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: 61,433Questions: 1Answers: 10,049 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: 192Questions: 54Answers: 4

    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.

  • raviksharma9021raviksharma9021 Posts: 1Questions: 0Answers: 0
    edited March 2019

    you can sort table like this date wise

    <table width="100%" class="display" id="datatable">
          <thead>
            <tr>
              <th>Col1</th>
              <th>Col2</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td>
                03/03/2017
              </td>
              <td>5</td>
            </tr>
            <tr>
              <td>
                03/04/2017
              </td>
              <td>4</td>
            </tr>
            <tr>
              <td>
                 03/05/2016
              </td>
              <td>2</td>
            </tr>
            <tr>
              <td>
                03/06/2016
              </td>
              <td>17</td>
            </tr>
            <tr>
              <td>
                03/07/2017
              </td>
              <td>10</td>
            </tr>
          </tbody>
        </table>
      </body>
    
    $(function() {
      var oTable = $('#datatable').DataTable({
        "oLanguage": {
          "sSearch": "Filter Data"
        },
        "iDisplayLength": -1,
        "sPaginationType": "full_numbers",
    
      });
    
    
    
    
      $("#datepicker_from").datepicker({
        showOn: "button",
        buttonImage: "images/calendar.gif",
        buttonImageOnly: false,
        "onSelect": function(date) {
          minDateFilter = new Date(date).getTime();
          oTable.fnDraw();
        }
      }).keyup(function() {
        minDateFilter = new Date(this.value).getTime();
        oTable.fnDraw();
      });
    });
    
    // Date range filter
    minDateFilter = "";
    maxDateFilter = "";
    
    $.fn.dataTableExt.afnFiltering.push(
      function(oSettings, aData, iDataIndex) {
        if (typeof aData._date == 'undefined') {
          aData._date = new Date(aData[0]).getTime();
        }
    
        if (minDateFilter && !isNaN(minDateFilter)) {
          if (aData._date < minDateFilter) {
            return false;
          }
        }
    
        return true;
      }
    );
    
  • klewitzkeklewitzke Posts: 1Questions: 0Answers: 0

    @srturgut, your solution worked perfectly for me.

  • aziegler3aziegler3 Posts: 21Questions: 3Answers: 0

    @srturgut , that is a simple solution that worked for me. Thank you

  • winkbracewinkbrace Posts: 1Questions: 0Answers: 0
    edited January 2020

    Because I am loading the data as json, I don't have the option of setting td attributes.
    I solved it by using a js function utilizing moment. This function will format the date based on the current locale, but sort based on the raw date input YYYY-MM-DD

    function(data, type) { 
        return type === 'sort' ? data : moment(data).format('L'); 
    }
    

    Complete working example:

    $(document).ready(function() {
        $('#my-data-table').DataTable({
            "data": [ {"employee_id": 1009423, "hire_date": "2019-02-01"} ],
            "columns": [
                {"data": "employee_id", "title": "Employee ID"}, 
                {"data": "hire_date", "title": "Hire Date", "render": function(data, type) {
                    return type === 'sort' ? data : moment(data).format('L');
                }}
            ],
            "order": [[1, "asc"]]
        });
    });
    
  • hyerkesshyerkess Posts: 1Questions: 0Answers: 0
    edited February 2020

    This worked a treat, thanks!

  • FonataFonata Posts: 1Questions: 0Answers: 0
    edited March 2020

    For us, the approach is to use a microformat like this <time datetime="2020-02-10">10.02.2020</time>. The microformat makes sure that DataTables sees a sortable version and humans see theirs.
    However, DataTables detects this cell type as a number and incorrectly sorts just by the visual numbers anyways, so we have to override the auto-detection via the columns array. See line 39 in this example. You won't need the orderDataType, just the type key.

  • blink38blink38 Posts: 1Questions: 0Answers: 0

    I tried Winkbrace solution which was working until it break for some unknown reasons.

    According to https://datatables.net/reference/option/columns.render , in render function, the type parameter is important, and I changed the date render function to :

    function(data, type){
    
         if (type == "sort"){
               return new Date(data).getTime();
         }
    
         return new Date(data).toLocaleString();
    }
    

    You can just return the data if type is equal to 'sort'. I choose to return the timestamp, thinking the sort will be faster.

  • JoniHellJoniHell Posts: 2Questions: 0Answers: 0
    edited March 2022

    On php, one solution could be to run the date into time using strtotime function.

    $date = "2022-03-11";
    
    $table_cell = "<td data-sort='" <?= strtotime( $date ) ?> "'>".$date." </td>";
    
  • MikeZ**MikeZ** Posts: 7Questions: 1Answers: 0

    The ASP.NET equivalent of the answer from @srturgut is as follows:

    strDate_Format_ISO = "{0:yyyy-MM-dd HH:mm}"
    strUser_Date_Format = "{0:dd-MMM-yyyy HH:mm}"

    <td data-sort="<%# Eval("My_Date_Field", strDate_Format_ISO) %>">
    <%# Eval("My_Date_Field", strUser_Date_Format)%>
    </td>

Sign In or Register to comment.