CODEWITHSUNDEEP
javascriptjqueryasp.net-mvcrazordatatables
Hennie Francis
Hennie Francis

Reputation: 124

jQuery DataTables: Weird sorting with Dates

I need to format my Date Column in a certain format, but also needs to be sorted as a Date. Its sorts it like text because of the .ToString. Any advise how to sort it as Date but still keep the format?

View:

<script>
    $(function () {
        $('#dataTableHistory').DataTable({
            "iDisplayLength": 50,
            "order": [[0, "desc"]]
        });
    });
</script>

<table id="dataTableHistory" class="display">


    <thead>
        <tr>
            <th>Date</th>
            <th>User</th>
            <th>Action</th>
        </tr>
    </thead>

    <tbody>
        @foreach (var o in (List<CompoundSheetHistory>)ViewData["CompoundSheetHistories"])
        {
            <tr>
                <td>@o.DateCreated.ToString("dd MMM yyyy HH:mm:ss")</td>
                <td>@o.User.FirstName</td>
                <td>@o.ActionDescription</td>
            </tr>
        }
    </tbody>
</table>

On the first load, and when I click on the sort button, it sort the column, what seems to be like text.

results of sorting

Upvotes: 1

Views: 865

Answers (3)

lofihelsinki
lofihelsinki

Reputation: 2571

Easiest way, but a bit of a hack. Just include a hidden span at the start of the cell with a alphabetically sortable date format:

<tbody>
    @foreach (var o in (List<CompoundSheetHistory>)ViewData["CompoundSheetHistories"])
    {
        <tr>
            <td>
              <span style="display:none">@o.DateCreated.ToString("yyyy-mm-dd HH:mm:ss")
              </span>@o.DateCreated.ToString("dd MMM yyyy HH:mm:ss")
            </td>
            <td>@o.User.FirstName</td>
            <td>@o.ActionDescription</td>
        </tr>
    }
</tbody>

Upvotes: 1

kapantzak
kapantzak

Reputation: 11750

You can provide the data as an array of objects and use the colums.render property.

Hold the date to display in DateCreated column and add another column (DateToSort) to your dataset holding the number of milliseconds of the specific date (date.getDate()):

var data = [
  {
    DateCreated: date.ToString("dd MMM yyyy HH:mm:ss"),
    DateToSort: date.getDate(),
    FirstName: ...,
    ActionDescription: ...
  },
  ...
]

$('#dataTableHistory').DataTable({
   data: data,
   columns: [
      {
         data: 'DateCreated',
         render: {
           _: 'DateCreated',
           sort: 'DateToSort'
         }
      },
      ...
   ]
});

You can check the documentation

Upvotes: 1

Shiffty
Shiffty

Reputation: 2156

I'd recommend including the datetime-moment plugin for datatables. It has automatic type detection and sorting, you just have to provide the format.

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


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

    $(function() {
      $('#dataTableHistory').DataTable({
        "iDisplayLength": 50,
        "order": [
          [0, "desc"]
        ]
      });
    });
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.20.1/moment.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/plug-ins/1.10.16/sorting/datetime-moment.js"></script>

<table id="dataTableHistory" class="display">
  <thead>
    <tr>
      <th>Date</th>
      <th>User</th>
      <th>Action</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>22 Nov 2017 10:15:55</td>
      <td>John</td>
      <td>Modified</td>
    </tr>
    <tr>
      <td>22 Nov 2017 10:15:55</td>
      <td>John</td>
      <td>Modified</td>
    </tr>
    <tr>
      <td>22 Nov 2017 10:15:55</td>
      <td>John</td>
      <td>Modified</td>
    </tr>
    <tr>
      <td>21 Nov 2017 10:13:55</td>
      <td>John</td>
      <td>Modified</td>
    </tr>
    <tr>
      <td>12 Jan 2018 11:23:21</td>
      <td>John</td>
      <td>Modified</td>
    </tr>
    <tr>
      <td>11 Nov 2017 08:14:55</td>
      <td>John</td>
      <td>Modified</td>
    </tr>
  </tbody>
</table>

Upvotes: 1

Related Questions