How to date sort as date instead of string

rdm

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

allan

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

rdm

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.

flamontagne

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.

srturgut

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.

raviksharma9021

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

klewitzke

@srturgut, your solution worked perfectly for me.

aziegler3

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

winkbrace

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

hyerkess

This worked a treat, thanks!

Fonata

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.

blink38

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.

JoniHell

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**

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>