Sort a column by date

lionellionel

Hello everyone,

I would like to be able to sort a date column in clicking on the title. I follow this tutorial but it doesn't work. I can click on the column title (the last one), but the order is wrong.

The format date is DD/MM/YYYY.

I implement it in this fiddle.

Could you please tell me what I did wrong please?

Thank you for your help,
Lionet

rf1234

https://jsfiddle.net/arvznxc4/2/

moment.js is case sensitive

lionellionel

I don't understand why it works in your example.

I replace 'dd/mm/yyyy' by 'DD/MM/YYYY' in my fiddle but it still didn't work. What are the others change you make? i don't find them.

rf1234

I only changed 9/10/2020 to 09/10/2020. That's all.

You would need to check moment.js to get single digit days working. Don't know whether that is possible at all.

lionellionel

Ok, thank you!

I put an example with a little bit more data and the two last columns are not sorted properly even if the data are well written.

https://jsfiddle.net/margarette/pj8590yr/6/

For example : 01/01/1975

Do you have an idea why?

rf1234

This on also works with single digit days and months:
https://jsfiddle.net/twc3qe2r/2/

Just changed a couple of dates and the date format to "D/M/YYY"

lionellionel

I applied your change in the fiddle with more data and it doesn't work

rf1234

Must be something else in your data table. Don't know. I am using the plug in with different moment.js locale files depending on the user language for various date formats and never had any problem. My data tables are database sourced using Editor.

lionellionel

arf

I reduced the number of data but I still have an issue. I am working on it.

it works in desc but not asc, weird.

lionellionel

I found the issue !

I add <th data-type='date'> wrote in the table, I don't know why but it made an issue.

Thank you very much for your help!

rf1234

You might want to take a look at the French locale file. It gives you a good overview of the various formats. You could use format "L" for example. Or bespoke formats as well.
Here is a link to the locale file:
https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/locale/fr.js

And this is from my own coding. I am using format "L" everywhere and various bespoke formats on different pages.

if (lang === 'de') {
    moment.locale('de');
    momentLocale = 'de';
} else {
    moment.locale('en-gb');
    momentLocale = 'en-gb';
}

//should be positioned after the number formatting to make sure
//date columns don't accidentally are assigned formatted-num
//set parms for date sorting using moment.js
if ( checkExists('#ctrLogPage') || checkExists('#contractLogPage') ||
     checkExists('#authLogPage')                                       ) {
    if (lang == 'de') {
        $.fn.dataTable.moment('DD.MM.YYYY, HH:mm:ss [Uhr]');
    } else {
        $.fn.dataTable.moment('DD/MM/YYYY, @ hh:mm:ss A');
    }
} else if ( checkExists('#contractGovPage') ) {
    if (lang == 'de') {
        $.fn.dataTable.moment('DD.MM.YYYY, HH:mm [Uhr]');
    } else {
        $.fn.dataTable.moment('DD/MM/YYYY, @ hh:mm A');
    }
}        
$.fn.dataTable.moment( 'L', momentLocale );

lionellionel

Thank you a lot!

Have a good day

rf1234

hmmm, in your fiddle the date ordering doesn't work, I am afraid.

lionellionel

In this one, it works
https://jsfiddle.net/margarette/pj8590yr/25/

rf1234

I had to tweak the plug in a little to make it work with empty date fields or other content in the column.

(function (factory) {
    if (typeof define === "function" && define.amd) {
        define(["jquery", "moment", "datatables.net"], factory);
    } else {
        factory(jQuery, moment);
    }
}(function ($, moment) {
 
$.fn.dataTable.moment = function ( format, locale ) {
    var types = $.fn.dataTable.ext.type;
 
    // Add type detection
    types.detect.unshift( function ( d ) {
        if ( d ) {
            // Strip HTML tags and newline characters if possible
            if ( d.replace ) {
                d = d.replace(/(<.*?>)|(\r?\n|\r)/g, '');
            }
 
            // Strip out surrounding white space
            d = $.trim( d );
        }
 
        // Null and empty values are acceptable
        if ( d === '' || d === null || d === "Summen per Laufzeitende" ||
                                       d === "Totals"                      ) {
            return 'moment-'+format;
        }
 
        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 ) {
        if ( d ) {
            
            if ( d === "Summen per Laufzeitende" ) {
                d = "31.12.2999";
            }
            if ( d === "Totals" ) {
                d = "31/12/2999";
            }
            // Strip HTML tags and newline characters if possible
            if ( d.replace ) {
                d = d.replace(/(<.*?>)|(\r?\n|\r)/g, '');
            }
 
            // Strip out surrounding white space
            d = $.trim( d );
        }
         
        return !moment(d, format, locale, true).isValid() ?
            Infinity :
            parseInt( moment( d, format, locale, true ).format( 'x' ), 10 );
    };
};
 
}));

Something like this could work for you:

(function (factory) {
    if (typeof define === "function" && define.amd) {
        define(["jquery", "moment", "datatables.net"], factory);
    } else {
        factory(jQuery, moment);
    }
}(function ($, moment) {
 
$.fn.dataTable.moment = function ( format, locale ) {
    var types = $.fn.dataTable.ext.type;
 
    // Add type detection
    types.detect.unshift( function ( d ) {
        if ( d ) {
            // Strip HTML tags and newline characters if possible
            if ( d.replace ) {
                d = d.replace(/(<.*?>)|(\r?\n|\r)/g, '');
            }
 
            // Strip out surrounding white space
            d = $.trim( d );
        }
 
        // Null and empty values are acceptable
        if ( d === '' || d === null  ) {
            return 'moment-'+format;
        }
 
        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 ) {
        if ( d ) {
            
            if ( d === '' || d === null  ) {
                d = "01/01/1001";
            }
            // Strip HTML tags and newline characters if possible
            if ( d.replace ) {
                d = d.replace(/(<.*?>)|(\r?\n|\r)/g, '');
            }
 
            // Strip out surrounding white space
            d = $.trim( d );
        }
         
        return !moment(d, format, locale, true).isValid() ?
            Infinity :
            parseInt( moment( d, format, locale, true ).format( 'x' ), 10 );
    };
};
 
}));

lionellionel

WOW! It is a so completed answer!

Thanks a lot!

rf1234

no problem - we all run into the same issues at some point in time
I think I posted something on SO on this some time ago ... Let me check ...

Here it is:
https://stackoverflow.com/questions/53238318/html5-javascript-datatables-order-sort-of-columns/53238593#53238593