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

samw

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?

tangerine

What does your DT initialization code look like?

kthorngren

This blog discusses the recommended way to sort by dates.

Kevin

samw

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
            }


    });

tangerine

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.

samw

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

kthorngren

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

samw

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

thank you for your help