Simple-DataTables Date Sorting Issue

Question

I am using fiduswriter/Simple-DataTables to display data in a data table view. One of the columns is a date column, coming in from MySQL using the following:

DATE_FORMAT(date, '%Y-%m-%dT%TZ') AS date

I then have the column specified in my configuration using:

{
    format: 'ISO_8601',
    render: function(data, cell, row) {
        if (!data) {
            return '';
        }
        const dateData = new Date(data);
        let formatted = dateData.toISOString().slice(0,10).replace(/-/g, '');
        return `${formatted.substring(4, 6)}/${formatted.substring(6)}/${formatted.substring(0, 4)}`;
    },
    select: 4,
    sort: 'desc',
    type: 'date',
}

What I expect this to do is:

The format: 'ISO_8601' recognize that these are dates coming in an ISO_8601 format
The render: function(data, cell, row) { ... } either return an empty string or return the date formatted in mm/dd/yyyy format
The select: 4 indicates that this is the 5th column (using 0 based index) of my incoming data
The sort: 'desc' indicates that I want the default sort to be descending
The type: 'date' indicates that the column is a date field

Then I load my data in using:

const request = {
    method: 'GET',
    headers: {
        'Accept': 'application/json'
    }
};
fetch(`/server/api/form/query.php?form_id=${objectId}`, request)
        .then(response => {
            if (response.status < 200 || response.status > 299) {
                throw response.json();
            }
            return response.json();
        })
        .then(formEntries => {
            formEntries = formEntries || {};
            formEntries.records = formEntries.records || '';
            const massagedData = formEntries.records.map(record => {
                const fields = record.Fields || '[]';
                const parsedFields = JSON.parse(fields) || {};
                const keys = Object.keys(parsedFields);
                const nameField = keys.find(key => parsedFields[key].name === 'Name');
                const nameValue = nameField ? parsedFields[nameField].value || '' : '';
                const emailField = keys.find(key => parsedFields[key].name === 'Email');
                const emailValue = emailField ? parsedFields[emailField].value || '' : '';
                const dateValue = record.CreatedOn || '';
                const entryStatusName = record.EntryStatusName || '';
                return [ record.EntryId, nameValue, emailValue, entryStatusName, dateValue ];
            });
            table.insert({ data: massagedData });
        })

This is loading and formatting my data just fine, but for whatever reason the date sorter is not working properly. It appears as if it is sorting by the month field rather than the actual newest field. E.g. I am getting:

How do I get the data table to properly sort date columns?

Edit - It appears that if I change the format of my data being returned in the renderer to yyyy/mm/dd then it works. However, this is not a suitable workaround. Ideally, I would be able to specify the desired format while having the sorter sort properly.

Simple-DataTables Date Sorting Issue

0 Answers