Date in MM/dd/yyyy format not sorting properly

glimpsed_chaos

Link to test case: https://live.datatables.net/mutizifa/1/
Error messages shown: None
Description of problem: Sorting appears to sort by month first, not year then month then day then time.

I've linked a test case where this is shown. I've tried using the method from Ording Formatted Dates (Luxon) but not helping resolve the issue.

allan

"occurred": "03/24/2023 08:59:53",

Is some of the example data, but you are defining:

DataTable.datetime('MM/dd/yyyy hh:mm:ss a');

which doesn't match since there is no am/pm in the data.

Using:

DataTable.datetime('MM/dd/yyyy hh:mm:ss');

Allows it to work as expected: https://live.datatables.net/mutizifa/2/edit .

Allan

glimpsed_chaos

@allan I can see where this fixes the issue in the test case. I even rolled back from the nightly to my current verions I am using and it works as well.

However, when I apply the same datatable.dateTime.js in my local project and add in the DataTable.datetime('MM/dd/yyyy HH:mm:ss'); it still did not work.

I even pulled in the entire test case to my local to ensure I had the libraries and settings correctly applied; this did work as it does in the test case. So, not a library issue or application issue.

I know it is doubtful, but would this be any different when making an ajax call?

I realize the order would be set in my query, but the actual sorting by datetime is not working under this condition.

Here's the setup (I did not include the preHxhr.dt, xhr.dt, layout, and other options to try and keep it limited):

 var table = $('#eventTable') 
           .DataTable({
                ajax: {
                    url: "@Url.Action("GetAllEvents")",
                    type: "POST",
                    contentType: 'application/json; charset=utf-8',
                    dataType: "json",
                    data: function (d) {
                        return JSON.stringify({
                            FilterOn: getFilters(),
                            columns: d.columns,
                            Draw: d.draw,
                            Length: d.length,
                            Order: d.order,
                            Search: d.search,
                            SortOrder: d.SortOrder,
                            Start: d.start,
                        });
                    },
                },
                processing: true,
                responsive: true,
                serverSide: true,
                search: {
                    return: false
                },
                columns: [
                    { data: 'child' },
                    { data: 'select' },
                    { data: 'occurred' },
                    { data: 'statusName' },
                    { data: 'priorityName' },
                    { data: 'severityName' },
                    { data: 'note' },
                    { data: 'email' },
                    { data: 'incident' },
                    { data: 'eventHost' },
                    { data: 'service' },
                    { data: 'sentBy' },
                    { data: 'eventId' },
                ],
                autoWidth: false,
                language: {
                    info: 'Showing _START_ to _END_ of _TOTAL_ Events',
                    infoFiltered: ' (filtered from _MAX_ Events)',
                    lengthMenu: '_MENU_ events per page',
                },
                stateSave: true,
                scrollCollapse: false,
                scroller: false,
                scrollY: '72vh',
                paging: true,
                lengthMenu: [[10, 15, 20, 50, 100], [10, 15, 20, 50, 100]],
                pageLength: 20,
                pagingType: "full_numbers",
                searching: { regex: true },
                select: {
                    style: 'multi',
                    selector: 'td:nth-child(2) input',
                    headerCheckbox: false
                },
                order: [[2, 'desc']]
            });

kthorngren

You have enabled server side processing with serverSide: true,. All sorting, searching and paging functions are performed by the server script. The DataTable.datetime( .. ) definitions work only with client side processing. The server script will need debugged and fixed to sort the datetime field correctly.

Can you post the relevant portion of the server script for the query?

What is the field definition for the datetime field?

Kevin

glimpsed_chaos

@kthorngren Thank you for pointing that out. It's just a datetime field in SQL. But my mistake was trying to perform a OrderByDynamic function after the table was returned such as this:

var sortedList = qTable.OrderByDynamic("DateField", DtOrderDir.Asc);

That would sort fine if it was not a datetime column.

I ended up just appending my query with:

query += $" ORDER BY {orderCriteria} {(orderAscendingDirection ? "ASC" : "DESC")}";

This works fine now.