Parsing date from text string to allow proper sorting

cgrucelski

I have a dynamically-generated table that contains a column with 'release dates' of products I would like to sort by date. The problem is that the values in this column are preceded by a number wrapped in brackets, so the dates are treated as text.

Values in this column, which is the third column in the table, include the date and time stamp with AM/PM designation, preceded by the number or releases or the word 'None.' For example, here are some actual values from this column:

[1] 1/24/2008 5:53:35 PM
[1] 7/31/2012 11:32:50 AM
[10] 3/29/2013 2:59:04 PM
[3] 12/17/2014 2:43:27 PM
[1] 1/9/2015 6:48:31 PM
[4] 1/9/2015 7:02:09 PM
[4] 1/30/2015 11:25:20 AM
[2] 12/27/2016 12:32:02 PM
[2] 11/16/2017 11:04:22 AM
None

In reading through the description of the 'Ultimate date/time sorting plugin' (https://datatables.net/blog/2014-12-18), I thought perhaps this might do the trick, but referencing the required 'moment.min.js' and 'datetime-moment.js' JavaScript files and registering the following date/time format did not work.

$.fn.dataTable.moment( 'M/D/YYYY h:m:s A' );

Is there a way to specify that the text preceding the date time stamp within this column should be ignored for sorting purposes, so that the data in the column can be sorted from oldest to newest and vice-versa, rather than by alphabetical text?

Thanks in advance!

cgrucelski

Here's a Fiddle: https://jsfiddle.net/cgrucelski/q2k5uxsc/15/

kthorngren

Is there a way to specify that the text preceding the date time stamp within this column should be ignored for sorting purposes,

I think orthogonal data is what you want to use. You would use columns.render and for the sort type remove the leading bracket/number. You might still need to use the moment.js plugin as described in the blog you linked to.

Kevin

cgrucelski

Just realized the link to the plug-in in my original post wasn't correct. This is the correct URL for the 'Ultimate Date / Time Sorting' plugin that I was referencing: https://datatables.net/blog/2014-12-18

cgrucelski

I've included my code below in case anyone else runs into a similar issue. I'm sure there's probably a better way to do this, as I'm not a programmer by trade. But, this is what I ended up with and it appears to be working as expected, so I'm rolling with it.

Initially, I set the target using the numeric ID of the column, but for my particular application, users have the option to dynamically hide one of the columns that precedes the column with the date, which threw off the index. So, I just assigned a class for the column instead.

I referenced both 'moment.min.js' and 'datetime-moment.js' in my page, along with 'jquery.dataTables.min.js' and 'jquery.dataTables.min.css'.

"columnDefs": [ { 
    // remove number of releases to allow sorting as dates
    "targets": "prdTblHdr_LiveRelease", // apply to column by class name
    "type": "date",
    "render": function ( data, type, row ) {
        // if sort or filter data is requested, format the date
        if ( type === 'sort' || type === 'filter' ) {
            if (data.indexOf(']') >= 0) {
                var dt = data.split("]")[1];
                dt = moment(dt, 'M/D/YYYY h:m:s A').format('M/D/YYYY');
                //console.log(dt);
                return dt;
            }
        }
        // otherwise just return data unaltered
        return data;
    }

} ],