order [[0, "desc"]] not sorting date column to descending.

lalitsingh82

I am using aspnetcore 2.2 MVC application. populating datatable from linq where I specified orderbydescending(x==> x.logintime) this column login time is formated to format to display as below

Display(Name = "Login Time")]
[DisplayFormat(DataFormatString = "{0:MM/dd/yyyy HH:mm}", ApplyFormatInEditMode = true)]
//[DataType(DataType.DateTime)]
public DateTime LoginTime { get; set; }

when I load the datatable, the first column is not getting sorted. what would be the issue ? please find attached data populated. I am using below sequence of js, css files

----below is the view page in mvc. where "UserAccessLogs" is the name of table-- table data is in attachment.

<script src="@Url.Content("~/lib/DataTables/DataTables-1.10.18/js/jquery.dataTables.js")"></script>
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables//DataTables-1.10.18/css/datatables.min.css")">
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/DataTables-1.10.18/css/jquery.dataTables.min.css")">
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/DataTables-1.10.18/css/dataTables.bootstrap4.min.css")" />
<script src="@Url.Content("~/lib/DataTables/Select-1.2.6/js/dataTables.select.min.js")"></script>
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/Select-1.2.6/css/select.bootstrap4.min.css")" />
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/Buttons/1.5.2/css/buttons.dataTables.min.css")" />
<script type="text/javascript">
$(document).ready(function () {

        $("#UserAccessLogs").DataTable({
            select: 'single',
            "lengthChange": false,
            "pageLength": 20,
            "pagingType": "full_numbers",
          **  "order": [[0, "desc"]]**
            //"columnDefs": [
            //    { "targets": 0, "visible": false }
            //]
        });
        //var table = $('#UserAccessLogs').DataTable();
        //$('#UserAccessLogs tbody').on('click', 'tr', function () {
        //    if ($(this).hasClass('selected')) {
        //        $(this).removeClass('selected');
        //    }
        //    else {
        //        table.$('tr.selected').removeClass('selected');
        //        $(this).addClass('selected');
        //    }
        //});
    });
</script>

}

also find attached output

kthorngren

This blog discusses how to use moment.js with a plugin to sort datetime fields in Datatables.

Kevin

colin

This thread here may also help - it discusses what to do when some of your cells are empty(as yours are).

C

lalitsingh82

I tried , it is still now working. figured out the data loads correctly in grid, but after fraction of seconds it reloads unsorted data.

tried by commenting "order": [[0, 'desc']] seems no effect.

kthorngren

I tried , it is still now working.

You tried to use the moment.js and the datetime-moment.js plugin?

If so then please at least post the code you used for this, for example: $.fn.dataTable.moment( 'HH:mm MMM D, YY' );. Maybe you didn't get the format correct. Or as Colin mentioned you have blank data or maybe something else in the column negating the plugin.

Looks like you should use this:
$.fn.dataTable.moment( 'MM/DD/YYYY HH:mm' );

The formatting options are listed here:
http://momentjs.com/docs/#/displaying/

figured out the data loads correctly in grid, but after fraction of seconds it reloads unsorted data.

Looks like you are using a dom sourced table. The first load is probably the native HTML table then you are seeing Datatables load.

Ideally it would help to have a link to your page or a test case with an example of your datetime data to help troubleshoot.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

lalitsingh82

@kthorngren ,@colin apologize for not pasting code. please find below.

----view page-----

<script src="@Url.Content("~/lib/DataTables/DataTables-1.10.18/js/jquery.dataTables.js")"></script>
<script src="@Url.Content("~/lib/DataTables/Plugins/moment.js")"></script>
<script src="@Url.Content("~/lib/DataTables/Plugins/datetime-moment.js")"></script>
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/DataTables-1.10.18/css/datatables.min.css")">
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/DataTables-1.10.18/css/jquery.dataTables.min.css")">
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/DataTables-1.10.18/css/dataTables.bootstrap4.min.css")" />
<script src="@Url.Content("~/lib/DataTables/Select-1.2.6/js/dataTables.select.min.js")"></script>
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/Select-1.2.6/css/select.bootstrap4.min.css")" />
<link rel="stylesheet" type="text/css" href="@Url.Content("~/lib/DataTables/Buttons/1.5.2/css/buttons.dataTables.min.css")" />
<script type="text/javascript">
$(document).ready(function () {
** $.fn.dataTable.moment('MM/dd/yyyy HH:mm');**

        $("#UserAccessLogs").DataTable({
            select: 'single',
            "lengthChange": false,
            "pageLength": 20,
            "pagingType": "full_numbers",
            "order": [[0, 'desc']]
        });
        var table = $('#UserAccessLogs').DataTable();
        $('#UserAccessLogs tbody').on('click', 'tr', function () {
            if ($(this).hasClass('selected')) {
                $(this).removeClass('selected');
            }
            else {
                table.$('tr.selected').removeClass('selected');
                $(this).addClass('selected');
            }
        });
    });
</script>

----Model class------

    [Display(Name = "Login Time")]
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy HH:mm}", ApplyFormatInEditMode = true)]
    [DataType(DataType.DateTime)]
    public DateTime LoginTime { get; set; }

    [Display(Name = "Logout Time")]
    [DataType(DataType.DateTime)]
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy HH:mm}", ApplyFormatInEditMode = true)]
    public DateTime? LogoutTime { get; set; }

    [Display(Name = "Created By")]
    public string CreatedBy { get; set; }

    [Display(Name = "Log Creation Date")]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy  HH:mm}", ApplyFormatInEditMode = true)]
    public DateTime CreatedTs { get; set; }

    [Display(Name = "Log Modified By")]
    public string LastModifiedBy { get; set; }

    [Display(Name = "Log Modified Date")]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy  HH:mm}", ApplyFormatInEditMode = true)]
    public DateTime LastModifiedTs { get; set; }

--Data is already attached to my question.

I tried altering the sequence of js , css and other datatables.

kthorngren

$.fn.dataTable.moment('MM/dd/yyyy HH:mm');

Take a look at the moment.js format doc I linked to. Notice that moment doesn't use lower case for the month, day or year formats. Did you try my suggestion?

$.fn.dataTable.moment( 'MM/DD/YYYY HH:mm' );

Kevin

lalitsingh82

@kthorngren , @colin

Found it is working now after modifying the moment plugin. there was extra space between MM/DD/YYYY AND HH:mm. also dd and yyyy were not in caps. so after using below , it is working now. thanks a ton all of you.

$.fn.dataTable.moment('MM/DD/YYYY HH:mm');

Regards,
Lalit Singh.