date not sorting correctly

date not sorting correctly

montoyammontoyam Posts: 568Questions: 136Answers: 5
edited April 2020 in Free community support

i think I am doing everything in my .net project as explained here: https://editor.datatables.net/examples/dates/formatting-client.html

but my dates are not sorting correctly. sorting in descending order I get:

4/6
4/5
4/2
4/14
4/1

controller:

                    .Field(new Field("Submissions.RecordAdded")
                        .Set(false)
                    )

javascript:

    <script type="text/javascript" charset="utf-8" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>
    <script type="text/javascript" charset="utf-8" src="//cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>

    $(document).ready(function () {
        $.fn.dataTable.moment('MM-DD-YYYY h:mm a');
.
.
.

 var SubmissionsTable = $('#Submissions').DataTable({
{ data: "Submissions.RecordAdded", render: $.fn.dataTable.moment('MM-DD-YYYY h:mm a') }

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 21,343Questions: 26Answers: 4,954

    4/1

    Doesn't look like your date format matches what you have for the moment function ('MM-DD-YYYY h:mm a'). The moment format docs are here:
    https://momentjs.com/docs/#/displaying/

    Kevin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    sorry, I don't understand what you mean about not matching.

  • kthorngrenkthorngren Posts: 21,343Questions: 26Answers: 4,954
    Answer ✓

    Is this the format of the dates in your column: 4/1?

    It doesn't match the format you are using the moment:
    $.fn.dataTable.moment('MM-DD-YYYY h:mm a');

    Maybe you can post a test case showing the dates and the sorting problem.

    Kevin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    the field is defined in SQL as datetime. The entries look like this:
    2020-03-26 12:12:36.000
    2020-03-26 12:29:31.000
    2020-03-26 12:35:01.000

    I had read that in the Model, dataTables needs dates to be defined as strings:

    public string RecordAdded { get; set; }
    

    in the controller I have it like this:

                        .Field(new Field("Submissions.RecordAdded")
                            .Set(false)
                        )
    

    then the js, from the examples posted seem to want this:

     <script type="text/javascript" charset="utf-8" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="//cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>
     
        $(document).ready(function () {
            $.fn.dataTable.moment('MM-DD-YYYY h:mm a');
    .
    .
    .
     
     var SubmissionsTable = $('#Submissions').DataTable({
    { data: "Submissions.RecordAdded", render: $.fn.dataTable.moment('MM-DD-YYYY h:mm a') }
    

    in the DataTable, they are rendering like '3/26/2020 12:12:36.000'

  • kthorngrenkthorngren Posts: 21,343Questions: 26Answers: 4,954

    For render: $.fn.dataTable.moment('MM-DD-YYYY h:mm a') } you will want to read up on the datetime plugin and make sure to install the plugin code. You may need to use $.fn.dataTable.render.moment( from, to ); to properly render the date. The from format to match 2020-03-26 12:12:36.000 and the to format matching format you want to display.

    For the sorting using this:

        $(document).ready(function () {
            $.fn.dataTable.moment('MM-DD-YYYY h:mm a');
    

    You will want to read this blog and make sure to install the appropriate plugin code. The format will match what you are displaying.

    Kevin

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    ah, now I get what you mean about the format not matching my data. I needed 'm/d/yyyy h:mm:ss A'. I didn't even catch that.

This discussion has been closed.