DataTables logo
≡

How to get date column to sort acs/desc when clicking on the column header

How to get date column to sort acs/desc when clicking on the column header

samwsamw Posts: 15Questions: 7Answers: 0
in Free community support

So I have two date fields in the datatable for status date, one for displaying data in the date format mm/dd/yyyy and the other which is hidden has a format of yyyy-mm-dd which i use for sorting (in desc order) when the table loads. So when the data loads everything looks good but when a user clicks on "status date" column header to sort by asc/desc it sorts it by month from the mm/dd/yyyy format instead of by the actual date. How do I get the displayed Status Date field to sort by date correctly when the user clicks on the column header?

This question has an accepted answers - jump to answer

Answers

  • tangerinetangerine Posts: 3,371Questions: 42Answers: 395

    What does your DT initialization code look like?

  • kthorngrenkthorngren Posts: 22,495Questions: 26Answers: 5,169

    This blog discusses the recommended way to sort by dates.

    Kevin

  • samwsamw Posts: 15Questions: 7Answers: 0

    here you go:

    $('#CustomerStatusTable').DataTable({
    "order": [[5, "desc"]],
    "ajax": {
    "url": "/Home/GetData",
    "type": "GET",
    "datatype": "json",
    "serverSide": true,
    "processing": true

            },

        "columns": [
            { data: "CustomerName", width: "20%" },
            { data: "CustomerTitle", width: "20%" },
            { data: "StatuseCode", width: "5%" },
            { data: "StatusName", width: "20%" },
            {
                data: "StatusDate", width: "10%",
                type: Date,
                render: function (value) {
                    var pattern = /Date\(([^)]+)\)/;
                    var results = pattern.exec(value);
                    var dt = new Date(parseFloat(results[1]));
                    var mmm = (dt.getMonth() + 1).toString();
                    //the tostring function prevents the statement below from putting a 0 before the months oct,nov ,dec 
                    var mm = mmm.length > 1 ? mmm : "0" + mmm;
                    var ddd = dt.getDate().toString();
                    var dd = ddd.length > 1 ? ddd : "0" + ddd;
                    return mm + "/" + dd + "/" + dt.getFullYear();

                }
            }
           , {
                data: "StatusDate", width: "5%",
                type: Date,
                render: function (value) {
                    var pattern = /Date\(([^)]+)\)/;
                    var results = pattern.exec(value);
                    var dt = new Date(parseFloat(results[1]));
                    var mmm = (dt.getMonth() + 1).toString();
                    //the tostring function prevents the statement below from putting a 0 before the months oct,nov ,dec 
                    var mm = mmm.length > 1 ? mmm : "0" + mmm;
                    var ddd = dt.getDate().toString();
                    var dd = ddd.length > 1 ? ddd : "0" + ddd;
                    return dt.getFullYear() + "-" + mm + "-" + dd;

                }
            }

        ],
        "columnDefs": [
            {
                "targets": [5],
                "visible": false
            }


    });
  • tangerinetangerine Posts: 3,371Questions: 42Answers: 395
    Answer ✓

    I think you're over-complicating this. When you mentioned a hidden field, I thought you had something like this:

                // Sort column 4 (dob_for_display) using data from column 5 (dob).
            {
                data: "dob_for_display", 
                orderData: [5]
            },
            {
                data: "dob",
                visible: false,
                searchable: false
            },

    where dob_for_display is the date formatted for your display and dob has the sortable format yyyy-dd-mm.

  • samwsamw Posts: 15Questions: 7Answers: 0

    I looked at the blog you referenced. it wasn't so clear but I put that code from that page in my document.ready and now the datatable doesn't load

  • kthorngrenkthorngren Posts: 22,495Questions: 26Answers: 5,169

    I put that code from that page in my document.ready and now the datatable doesn't load

    Check your browser's console for errors.

    Kevin

  • samwsamw Posts: 15Questions: 7Answers: 0

    the errors were from the moment js file. i got it working by using orderData:[5]

    thank you for your help

This discussion has been closed.