JQuery Datatable Date Column Not Sorting Correctly

Question

I'm developing an Asp.Net MVC 5 site which uses JQuery Datatables http://www.datatables.net/ to display tabular data to the user. I have got the code working well, i.e., displaying, filtering, paging etc. I also have sorting working on most columns, except for my two date columns (RegisteredDate and LastLoginDate).

Razor View

<script>
        $(document).ready(function () {
            $('#dataTables-example').dataTable({  
                "bServerSide": true,  
                "sAjaxSource": "/Doctor/GetAjaxData",
                "bProcessing": true,
                "bJQueryUI": true,
                "aoColumns": [
                          { "sName": "DoctorID", "visible": false },
                          {
                              "mData": null,
                              "mRender": function (data, type, full) {
                                  return "<a href='/Admin/Doctor/DoctorDetails/" + full[0] + "'>" + full[1] + "</a>";

                              }
                          },
                          null, //Email
                          { "sName": "Doctor_GMC", "bSortable": false }, //GMC
                          null, //RegisteredDate
                          null, //LastLoginDate
                          {
                              "mData": null,
                              "mRender": function (data, type, full) {
                                  return "<button class='btn btn-danger btn-xs' id='btnDelete' data-toggle='modal' data-target='#myModal' data-id='"+ full[0] +"'>Delete</button>";

                              }
                          }

                ]
            });  
        });  
</script>

MVC Controller

        public JsonResult GetAjaxData(JQueryDataTableParamModel param)
        {
            IEnumerable<doctor> allDoctors;

            allDoctors = _doctorService.GetAllDoctors();

            var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
            Func<doctor, string> orderingFunction = (c => sortColumnIndex == 1 ? c.Doctor_FName :
                                                        sortColumnIndex == 2 ? c.Doctor_Email :
                                                        sortColumnIndex == 4 ? c.RegisteredDate.Value.ToString("dd/MM/yyyy") :
                                                        c.LastLoginDate.Value.ToString("dd/MM/yyyy"));

            IEnumerable<doctor> filteredDoctors;
            if (!string.IsNullOrEmpty(param.sSearch))
            {

                    filteredDoctors = _doctorService.GetAllDoctors()
                            .Where(d => d.Doctor_FName.ToUpper().Contains(param.sSearch.ToUpper())
                                        ||
                                        d.Doctor_LName.ToUpper().Contains(param.sSearch.ToUpper())
                                        ||
                            d.Doctor_Email.ToUpper().Contains(param.sSearch.ToUpper()));

            }
            else
            {
                filteredDoctors = allDoctors;
            }

            var displayedDoctors = filteredDoctors;

            var sortDirection = Request["sSortDir_0"]; // asc or desc
            if (sortDirection == "asc")
                displayedDoctors = displayedDoctors.OrderBy(orderingFunction);
            else
                displayedDoctors = displayedDoctors.OrderByDescending(orderingFunction);

            displayedDoctors = displayedDoctors
                        .Skip(param.iDisplayStart)
                        .Take(param.iDisplayLength);

            var aaData = displayedDoctors.Select(d => new string[] { Convert.ToString(d.DoctorID), d.NameFull, d.Doctor_Email, d.Doctor_GMC, d.RegisteredDate.Value.ToString("dd/MM/yyyy"), d.LastLoginDate.Value.ToString("dd/MM/yyyy") }).ToArray();

            return Json(new
            {
                sEcho = param.sEcho,
                aaData = aaData,
                iTotalRecords = allDoctors.Count(),
                iTotalDisplayRecords = filteredDoctors.Count()
            }, JsonRequestBehavior.AllowGet);

        }  

Both RegisteredDate and LastLoginDate should be UK dates, i.e., displayed in dd/mm/yyyy. They remain in this format, however, the sort doesn't seem to sort based on date, it just seems to be seeing the dates as a normal string.

In my controller action, you'll be able to see I've tried to keep the date as a uk format date in attempt that the sort will work correctly, e.g., but unfortunately it still doesn't sort correctly.

sortColumnIndex == 4 ? c.RegisteredDate.Value.ToString("dd/MM/yyyy") : c.LastLoginDate.Value.ToString("dd/MM/yyyy"));

var aaData = displayedDoctors.Select(d => new string[] { Convert.ToString(d.DoctorID), d.NameFull, d.Doctor_Email, d.Doctor_GMC, d.RegisteredDate.Value.ToString("dd/MM/yyyy"), d.LastLoginDate.Value.ToString("dd/MM/yyyy") }).ToArray();

Does anyone have any advice as to where I am going wrong with this, or how it can be fixed?

Any help greatly appreciated.

Thanks.

Answer 1

You can use object instead of string datatype:

Func<doctor, object> orderingFunction

Answer 2

It would work if you kept the date as a DateTime type, so do this:

if(sortColumnIndex == 1 || sortColumnIndex == 2)
{
    Func<doctor, string> orderingFunction = (c => sortColumnIndex == 1 ? c.Doctor_FName : c.Doctor_Email);
}
if(sortColumnIndex == 4)
{
    Func<doctor, DateTime> orderingFunction = c => c.LastLoginDate);
}

Because orderingFunction can have different datataypes, you'll probably have to do the sorting within each condition, but it can be done on 1 line, e.g:

if(sortColumnIndex == 4)
{
    Func<doctor, DateTime> orderingFunction = c => c.LastLoginDate);
    filteredDoctors = Request["sSortDir_0"] == "asc" ? filteredDoctors.OrderBy(orderingFunction) : filteredDoctors.OrderByDescending(orderingFunction);
}