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

datatable view

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

Maybe you can add a hidden column by convert date into timetamp(long), and the sort by it, ref: stackoverflow.com/a/32669709/9041712 — Yu-Lin Chen
– Yu-Lin Chen, Commented Dec 12, 2017 at 4:56
@Yu-LinChen, I was going to suggest the same, I've done exactly this many times before, its easier to deal with a long. — SPlatten
– SPlatten, Commented Dec 12, 2017 at 6:48
@LeeYuanYee, I add the sample code below: stackoverflow.com/a/47775236/9041712 — Yu-Lin Chen
– Yu-Lin Chen, Commented Dec 12, 2017 at 14:40

Sunil Hari · Accepted Answer · 2017-12-12 07:41:12Z

2

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

edited Dec 12, 2017 at 7:41

answered Dec 12, 2017 at 6:26

Sunil Hari

1,7941 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Lee Yuan Yee Over a year ago

may i know if there is two different date format in one datatable. how u will handle it? one date format is dd-MMM-yyyy hh:mm tt and another one is dd-MMM-yyyy

Sunil Hari Over a year ago

@LeeYuanYee Will modify the code that it sorts if hh:mm part is empty

Lee Yuan Yee Over a year ago

How will it be looks like?

Sunil Hari Over a year ago

@LeeYuanYee . Modified.Please check.

Sunil Hari Over a year ago

Nope it wont.So here am trying to split it by space.Normal case it would be dd -MMM -yyyy hh:mm tt and it would get split into 3.Now in our special case it would only have dd-MMM-yyyy part

|

davidkonrad · Accepted Answer · 2017-12-12 07:57:43Z

2

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>

edited Dec 12, 2017 at 7:57

answered Dec 12, 2017 at 7:52

davidkonrad

85.7k17 gold badges211 silver badges273 bronze badges

1 Comment

Sunil Hari Over a year ago

Sir,Date.parse('02-Jan-2017 12:00 AM') is not working in ie 11 and not in FF 57(Quantum).It is working in Chrome though

Yu-Lin Chen · Accepted Answer · 2017-12-12 14:39:14Z

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

andy · Accepted Answer · 2020-06-11 08:00:14Z

0

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

answered Jun 11, 2020 at 8:00

andy

1

Collectives™ on Stack Overflow

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

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related