Sorting by date not working as the date seems to be a string

MHCCAIRTeam

I have a field which is a date in my database. I have it added to the request/response as follows:

               .Field(new Field("DueDate").Validator(Validation.DateFormat(
                       Format.DATE_USA,
                       new ValidationOpts { Message = "Please enter a date in the format MM-dd-yyyy" }
                   ))
                   .GetFormatter(Format.DateSqlToFormat(Format.DATE_USA))
                   .SetFormatter(Format.DateFormatToSql(Format.DATE_USA))
               )

I followed some instructions to use moment.js to get sorting to work:

                $.fn.dataTable.moment = function (format, locale) {
                var types = $.fn.dataTable.ext.type;


                // Add type detection
                types.detect.unshift(function (d) {

                    return moment(d, format, locale, true).isValid() ?
                        'moment-' + format :
                        null;
                });

                // Add sorting method - use an integer for the sorting
                types.order['moment-' + format + '-pre'] = function (d) {

                    return moment(d, format, locale, true).unix();
                };

However, it doesn't seem to have any effect. The column sorts but it seems to be sorted as a string and not a date. Any suggestions on how to get column sorting by date MM-dd-yyyy? Thanks!

allan

Hi,

Have you called the plug-in? You need to add:

$.fn.dataTable.moment( 'MM-dd-YYYY' );

just before you initialise your DataTable. That will create a dynamic plug-in which will use MomentJS to sort the date as required.

Another option would be to use orthogonal data, but lets go with this one for now, and when its working we can talk about orthogonal data more if you like .

Allan

Falco

I use the "data-order" attribute in my table cells
I create the table with asp.NET so my tables are made like this

TableCell cell = new TableCell() { Text = date.ToString("dd.MM.yyyy HH:mm:ss.fffff") };
cell.Attributes.Add("data-order", date.Ticks)
row.Add(cell)
[...] // more cells here
table.Rows.Add(row)
[...] // more rows here

this orders the dates by whatever you want, in this example the integer ticks, then there is no need to fuzz about with plugins or special data formats.

MHCCAIRTeam

Hi Allan,
Thanks! I added the call to the plugin: $.fn.dataTable.moment('MM-dd-YYYY');

but it didn't seem to have any effect It looks to be sorting by MM-dd and ignoring year. So my rows are ordered ascending like this:

06-25-2019
07-03-2018
07-25-2018
07-25-2019
07-26-2017  

If I remove the get and set formatters and don't apply any client side formatting I get:

6/25/2019 12:00:00 AM 
7/03/2018 12:00:00 AM 
7/25/2018 12:00:00 AM 
7/25/2019 12:00:00 AM 
7/26/2017 12:00:00 AM 

I have to add def and dateFormat on my editor otherwise I get the completely wrong dates when updating inline:
Wed, 2 Jan 2013
Sat, 26 Jan 2013
Fri, 4 Jan 2013
Thu, 3 Jan 2013
Fri, 4 Jan 2013

Adding this to editor everything looks good while editing:

label: "Start Date:",
name: "StartDate",
type: 'date',
def: function () { return new Date(); },
dateFormat: "mm-dd-yy"

I am getting my data for data tables via an ajax call.

Thanks Falco! I'll have to do some additional reading since I am populating my table using an ajax call I'm not sure how to add the "data-order" attribute

allan

dateFormat: "mm-dd-yy"

This should be:

dateFormat: "MM-dd-YYYY"

Its a Momenet formatting string.

I'm not sure why the sorting isn't working though. Can you use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Allan

MHCCAIRTeam

Hey Allan,
I had thought it would be but whats weird is I changed the date format to MM-dd-YYYY and then the inline editor displays something like: January-02-YYYY Which doesn't make any sense to me at all. Debug code is: iqakax. Thanks!

allan

I think I see the issue with the input formatting going wonky! Use datetime rather than date. The former uses Moment (if it is loaded) while the latter uses jQuery UI (if it is loaded). datetime is the one I would recommend!

For the sorting, I'm wondering if it is this entry which is causing the issue:

    }, {
        "DT_RowId": "row_338",
        "Actions": {
            "ActionPK": "338",
            "GoalPK": "199",
            "LastModifiedDate": null,
            "ModifiedBy": "672720",
            "CreatedDate": "9/12/2017 8:21:40 AM",
            "ResponsiblePerson": "672720",
            "DueDate": null,
            "Description": "asdf",
            "isCompleted": null,
            "StartDate": null,
            "Hours": null
        },
        "Goals": {
            "GoalDescriptionLast": "sdfgdsfg",
            "GoalDescriptionConfirmed": null
        }
    }, {

DueDate is null. Could you change that to a valid date, or remove that entry and see if that resolves the issue?

The sorting extension does appear to have installed correctly.

Thanks,
Allan

MHCCAIRTeam

Hey Allan,
Sorry for the delayed response - I was out of office. Are null values a problem?
I am running in a test environment so its garbage data right now but nulls could potentially be there when running in production. I figured they would be lumped together at the top or bottom with everything else sorted by the column (in this case StartDate or DueDate)? I added values just to be safe for further testing.

With this server side:

        .Field(new Field("DueDate").Validator(Validation.DateFormat(
                Format.DATE_USA,
                new ValidationOpts { Message = "Please enter a date in the format MM-dd-yyyy" }
            ))
            .GetFormatter(Format.DateSqlToFormat(Format.DATE_USA))
            .SetFormatter(Format.DateFormatToSql(Format.DATE_USA))

and this client side:

Editor field
{

                        label: "Due Date",

                        name: "DueDate",
                        type: 'date',
                        def: function () { return Date(); },
                        dateFormat: "mm-dd-yy"

                        },

DataTable column:
{
data: "DueDate"
},

everything works except sorting.

Changing from dateto datetime fixed the issue with the dateFormat string taking jQuery UI formats rather than moment.js. I no longer need these two lines either:

                    def: function () { return Date(); },
                    dateFormat: "MM-dd-yyyy" //moment.js format since type is datetime

However, sorting is still treating values as strings it looks like. Dates are sorted:

6/25/2019 
7/03/2018 
7/25/2018 
7/25/2019 
7/26/2017

Removing the get and set formatter server side changes the format to have "h:mm:ss a" despite the call to $.fn.dataTable.moment('MM-dd-YYYY');

6/25/2019 12:00:00 AM
7/03/2018 12:00:00 AM
7/25/2018 12:00:00 AM
7/25/2019 12:00:00 AM
7/26/2017 12:00:00 AM

I'm at a bit of a loss Not sure what is going on as it seems I can effect formatting but it doesn't seem to change how its sorted.

allan

$.fn.dataTable.moment('MM-dd-YYYY');

Should be:

$.fn.dataTable.moment('MM-DD-YYYY');

That's my fault from above - sorry! The Moment docs show DD should be used for the day of the month with a leading zero.

Failing that, can you copy and paste some of the dates in your table? I'm surprised by the / in the dates in your previous post as the DATE_USA uses a - separator.

Allan

MHCCAIRTeam

Also, after changing to datetime from date the datepicker CSS changed (assuming this is now using moment). However, when selecting a date from the datepicker the format is yyyy-MM-dd. Removing the type all together sorting is the same and no datepicker.

MHCCAIRTeam

Hey Allan,
Sorry that was my mistake when typing out the dates. The dates use a - separator just like the DATE_USA.

I updated MM-dd-YYYY to be MM-DD-YYYY

and everything is working!! Thank you for all the help - you're awesome!

MHCCAIRTeam

So it looks like the issue that is causing sorting to break is the null dates - do you have any suggestions on how to handle them? Is it possible to keep null dates and allow sorting by date if its not null?

MHCCAIRTeam

I found the issue with null dates not sorting was caused by this in my client side code:

               $.fn.dataTable.moment = function (format, locale) {
                    var types = $.fn.dataTable.ext.type;


                    // Add type detection
                    types.detect.unshift(function (d) {

                        return moment(d, format, locale, true).isValid() ?
                            'moment-' + format :
                            null;
                    });

                    // Add sorting method - use an integer for the sorting
                    types.order['moment-' + format + '-pre'] = function (d) {

                        return moment(d, format, locale, true).unix();
                    };
                };

Naturally it was conflicting with this:
<script type="text/javascript" src="https://cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>

Also, correctly adding "format" to the fields fixed the formatting issue with the datepicker.

allan

Perfect! Yes, it looks like I updated the moment plug-in for ordering at some point to include the ability for it to support null.

So the table sorts correctly, the date format displayed is US style, the date picker works in Editor and all is good?

Allan

MHCCAIRTeam

Hey Allan,
Yes, everything is working perfectly after getting it configured correctly. Thank you for all the help! Data tables is amazing and you are truly awesome!!