sorting of datatable for date, datetime and time column

bczm8703

the column in my table will be dynamically created on runtime.
the table might have either date or DateTime or time, all 3 types, either of the 2 types or one of them
Currently, my datatable sorting for these columns are treated as a string during the sort
the format of the data will be like the following
date: 17/07/2021
DateTime: 23/04/2021 15:45
time: 16:34

colin

Yep, there would be no automatic search that could handle that - for example, how should it compare "17/07/2021" to "16:34"? Which one should be considered before the other?

That said, you can create your own sort plugin, here are a few you could use as a template, in which you would define your own logic.

Colin

bczm8703

there will not be a chance for a single column to have a different type. it is just that before runtime i would not be able to know which column will be date, or which column will be datetime or time

allan

Use this plug-in to register the date / time formats you want to be able to sort. DataTables will then automatically be able to detect columns with those formats.

Allan

bczm8703

hi. sorry for the late response. i have register the plugin before initialising the datatable. but the column seems to still be treated as string

Code

function InitComponent() {
        try {
            $.fn.dataTable.moment('DD/MM/YYYY HH:mm');
            $.fn.dataTable.moment('DD/MM/YYYY HH:mm:ss');
            $.fn.dataTable.moment('DD/MM/YYYY');
            $.fn.dataTable.moment('HH:mm');
if ($('#<%= gvLevel3Data.ClientID %>').is(":visible")) {
                if ($.fn.dataTable.isDataTable('#<%= gvLevel3Data.ClientID %>')) {
                    table = $('#<%= gvLevel3Data.ClientID %>').DataTable();
                }
                else {
                    table = $('#<%= gvLevel3Data.ClientID %>').DataTable({
                        order: [[2, 'desc']],
                        "columnDefs": [
                            { "orderable": false, "targets": [0, 1] },
                        ],
                        "dom": '<"top"lp<"clear">>rt<"bottom"ip<"clear">>'
                    });
                    //table.columns.adjust().draw();
                }
            }
}
        catch (err) {

        }


    }

Image of data

bczm8703

hi. i seem to find the cause of the sorting issue. if the column has an empty string, it will cause datatable to treat the whole column as string data causing the sorting issue mentioned above. is there any way I can handle this type of scenario?

colin

Blank entries shouldn't be the case - see "Ashton Cox" here - that would only happen if the "blank entries" have string type properties, such as white space or HTML formatting.

Could you look at that, please, and see if it helps. If it's still not working for you, please can you update my example, or link to your page, so that we can see the problem.

Cheers,

Colin