How to sort date with dd-MMM-yyyy hh:mm tt format in datatable

Question

I'm currently working on datatable, I found my sorting for date column was not working. here was my screenshot

Here was my code

<table id="tbl" class="table table-small-font table-bordered table-striped">
  <thead>
    <tr>
      <th>&nbsp;</th>
      <th class="text-left">Dated</th>
      <th class="text-left">Day</th>
      <th class="text-center">Remarks</th>
      <th class="text-center">State</th>
      <th class="text-center"></th>
    </tr>
  </thead>
  <tbody>
    @{ IEnumerable
    <PublicHoliday> PublicHolidays = (IEnumerable
      <PublicHoliday>)ViewData["PublicHolidays"]; int Idx = 1; } @foreach (var i in PublicHolidays) {
        <tr>
          <td>@Idx</td>
          <td>@i.Dated.ToString("dd-MMM-yyyy hh:mm tt")</td>
          <td>@i.Dated.ToString("ddd")</td>
          <td>@Html.DisplayFor(x => i.Remarks)</td>
          <td>@i.ForStateName</td>
          <td><a data-toggle="tooltip" title="Delete" onclick="DeleteRecord(@i.Id);"><span class="glyphicon glyphicon-remove"></span></a></td>
        </tr>
        Idx++; }
  </tbody>
</table>
<script src="~/lib/jquery-ui-1.12.1/jquery-ui.min.js"></script>
<script src="~/js/jquery.dataTables.min.js"></script>
<script src="~/js/dataTables.bootstrap.min.js"></script>
<script type="text/javascript">
  $(document).ready(function() {
    var tbl = $('#tbl').DataTable({
      dom: "<'row'<'col-sm-6 col-xs-7'l><'col-sm-6 col-xs-7'f>>" + "rtip",
      order: [
        [0, "asc"]
      ],
      pagingType: "numbers",
      iDisplayLength: 50
    });
  });
</script>

the sort column does not work at all, and I can't find any plugin from datatable to use. anyone please help.. thanks in advance

Answer 1

You can add the following sorting method to your options to sort according to date and time , specially if your time in 12 hour(AM/PM) format. I use the following code and it works for me:

$('#tableid').DataTable({
                "order": [[, 'desc']],//order column in descending order.
                "columnDefs": [
                    { "type": "date", "targets":  }//date column formatted like "03/23/2018 10:25:13 AM".
                ],
                "pageLength": 25
            });

Answer 2

I use momentjs and lodash, it would like:

var records = [
    ["1", "28-Jan-2017 12:00 AM"],
    ["1", "28-May-2017 12:00 AM"],
    ["1", "28-Mar-2017 12:00 AM"]
];

records = _.map(records, record => {
    record.push(moment(record[1], "DD-MMM-YYYY hh:mm A").unix());
    return record;
});

$(document).ready(function() {
    $('#example').DataTable( {
        data: records,
        columns: [
            { title: "id" },
            { title: "date" },
            { title: "ts"}
        ],
        'columnDefs': [
            { 'orderData':[2] },
            {
              'targets': [2],
              'visible': false
            },
        ],
    });
});

$(document).ready(function() {
    $('#example').DataTable( {
        data: records,
        columns: [
            { title: "id" },
            { title: "date" },
            { title: "ts"}
        ],
        'columnDefs': [
                    { 'orderData':[2] },
                    {
                    'targets': [2],
                    'visible': false
                    },
                ],
    });
});

Here is the jsFiddle

Answer 3

Since 02-Jan-2017 12:00 AM and so on is valid RFC2822 dates, all you have to do is to set the column type to date :

columnDefs: [
  { targets: 1, type: 'date' }
]

Or if you have some odd values in the data, like null values you can just pass back the parsed value and sort as number, by that forcing correct sorting anyway (I guess that is the real problem) :

columnDefs: [
  { targets: 1, 
    type: 'num',
    render: function(data,type) {
      if (type == 'sort') return Date.parse( data ).valueOf() 
      return data
    }
  }
]

There is really no need for a special sorting plugin here. A third option is to set the unformatted date as data-sort attribute on the itself

<td data-sort="@i.Dated.ToString()">@i.Dated.ToString("dd-MMM-yyyy hh:mm tt")</td>

Answer 4

I am not really sure that there is any plugin that supports date-dd-MMM-yyyy hh:mm tt format

So i have made modification to the plugin to support this format.

Here is the code for it.Load this piece of code after loading the datatable plugin

   (function() {

       var customDateDDMMMYYYYToOrd = function(date) {
         var dateTime = date.split(' '),
           dateObj = new Date(dateTime[0].replace(/-/g, ' ')),
           time = "00:00",
           type = "AM";
         if (dateTime.length > 1) { // if time part and am/pm part is available
           time = dateTime[1],
             type = dateTime[2];
         }

         var splitTime = time.split(":"),
           hours = type == "PM" ? Number(splitTime[0]) + 12 : Number(splitTime[0]),
           minutes = Number(splitTime[1]),
           seconds = 0,
           milliseconds = 0;
         return new Date(dateObj.getFullYear(), dateObj.getMonth(), dateObj.getDate(), hours, minutes, seconds, milliseconds);
       };

       // This will help DataTables magic detect the "dd-MMM-yyyy" format; Unshift
       // so that it's the first data type (so it takes priority over existing)
       jQuery.fn.dataTableExt.aTypes.unshift(
         function(sData) {
           "use strict"; //let's avoid tom-foolery in this function
           if (/^([0-2]?\d|3[0-1])-(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)-\d{4}/i.test(sData)) {
             return 'date-dd-mmm-yyyy';
           }
           return null;
         }
       );

       // define the sorts
       jQuery.fn.dataTableExt.oSort['date-dd-mmm-yyyy-asc'] = function(a, b) {
         "use strict"; //let's avoid tom-foolery in this function
         var ordA = customDateDDMMMYYYYToOrd(a),
           ordB = customDateDDMMMYYYYToOrd(b);
         return (ordA < ordB) ? -1 : ((ordA > ordB) ? 1 : 0);
       };

       jQuery.fn.dataTableExt.oSort['date-dd-mmm-yyyy-desc'] = function(a, b) {
         "use strict"; //let's avoid tom-foolery in this function
         var ordA = customDateDDMMMYYYYToOrd(a),
           ordB = customDateDDMMMYYYYToOrd(b);
         return (ordA < ordB) ? 1 : ((ordA > ordB) ? -1 : 0);
       };

     })();

The above code is the modification of date sort plugin(dd-mmm-yyyy).I have modified the customDateDDMMMYYYYToOrd function to fit this specific example

Add this so that the plugin would know what to be use when date is sorted

   var tbl = $('#tbl').DataTable({
    dom: "<'row'<'col-sm-6 col-xs-7'l><'col-sm-6 col-xs-7'f>>" + "rtip",
    order: [[0, "asc"]],
    pagingType: "numbers",
    pageLength: 50,
    columnDefs: [
      { type: 'date-dd-mmm-yyyy', targets: 1 } //add this part
    ]   
});

You can find the list of sorting plugins available for datatable here

Note:

Please note that this plug-in is **deprecated*. The datetime plug-in provides enhanced functionality and flexibility