Custom date format with sorting

andyfurniss

I'm trying to set up some date columns with a custom date format. I started with format 'ddd DD MMM YYYY' but realised this would be too long so I've now changed to 'ddd DD/MM/YYYY' to save a bit of horizontal space. All the dates and sorting were working fine when I was using 'ddd DD MMM YYYY'.

Since changing the format, I'm getting a weird 'Invalid Date' issue.

HTML

<table id="ops-br-table" class="table-full-width table table-striped table-minimal-padding">
    <thead>
        <tr>
            <th>Request Date</th>
            <th>Operator</th>
            <th>Tour ID</th>
            <th>Tour Name</th>
            <th>Duration</th>
            <th>Comments</th>
            <th>Name</th>
            <th>Email Address</th>
            <th>Brochure Sent</th>
            <th>Planned First Chase</th>
            <th>First Chase</th>
            <th>Planned Second Chase</th>
            <th>Second Chase</th>
        </tr>
    </thead>
    <tbody>

    </tbody>
</table>

JavaScript

$.fn.dataTable.moment('ddd DD/MM/YY');
$('#ops-br-table').DataTable({
    ajax: {
        url: settings.apiBaseUrl + 'interactions/brochurerequests',
        dataSrc: ''
    },
    columns: [
        {
            data: 'requestDate',
            type: 'date',
            render: function (data, type, row) { return data ? moment(data).format('ddd DD/MM/YY') : ''; }
        },
        { data: 'tourDetail.operator.name' },
        { data: 'tourId', width: "5%"  },
        { data: 'tourDetail.tourName', width: "10%" },
        { data: 'tourDetail.tourDuration' },
        { data: 'userComment', width: "15%" },
        { data: 'user.friendlyIdentifier' },
        { data: 'user.email' },
        {
            data: 'sentDate',
            type: 'date',
            render: function (data, type, row) { return data ? moment(data).format('ddd DD/MM/YY') : ''; }
        },
        {
            data: 'plannedFirstChaseDate',
            type: 'date',
            render: function (data, type, row) { return data ? moment(data).format('ddd DD/MM/YY') : ''; }
        },
        {
            data: 'firstChaseDate',
            type: 'date',
            render: function (data, type, row) { return data ? moment(data).format('ddd DD/MM/YY') : ''; }
        },
        {
            data: 'plannedSecondChaseDate',
            type: 'date',
            render: function (data, type, row) { return data ? moment(data).format('ddd DD/MM/YY') : ''; }
        },
        {
            data: 'secondChaseDate',
            type: 'date',
            render: function (data, type, row) { return data ? moment(data).format('ddd DD/MM/YY') : ''; }
        }
    ],
    order: [
        [ 0, "desc"]
    ],
    pageLength: 25
});

Here's a Fiddle without the AJAX dependency: https://jsfiddle.net/rk6e10ft/

Interestingly, in the fiddle, the only date that works is "Mon 11/06/18" but if I change this to "Mon 20/06/18" then it breaks too. It's like it's getting the day and month values confused or something. Is it something to do with the forward slashes? If so, I've already tried escaping them with square brackets as described here: https://stackoverflow.com/questions/28241002/moment-js-include-text-in-middle-of-date-format which did not work.

EDIT: Updated fiddle and removed escaping square brackets from formats.

colin

Hi @andyfurniss ,

This here is working - I've updated the version of Moment (not sure if needed), but crucially, removed the rendering. As you've declared the format at the top of the script, it's not necessary to do it in all other places,

Cheers,

Colin

andyfurniss

Thanks @colin.

The fiddle isn't entirely reflective of what's actually going on. The dates I get from the database are ISO8601 (e.g. 2018-11-19T00:00:00). I need the render option otherwise they don't get formatted. The dates are also nullable which is the reason for the date ? moment(date).format('ddd DD MMM YY') : ''. If the date is null then DataTables shows 'Invalid Date' so I'm replacing this with an empty string.

I've created a new fiddle to demonstrate this - https://jsfiddle.net/uanvhtg4/

Original issue is still present.

colin

Hi @andyfurniss ,

The best thing to do would be to change the default string for invalid dates, like this:

moment.updateLocale(moment.locale(), { invalidDate: "" });

See here,

Cheers,

Colin

andyfurniss

Thanks @colin. I wasn't aware of this setting!