Sort a column by date

lionellionel · August 2021

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 · August 2021

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

moment.js is case sensitive

lionellionel · August 2021

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 · August 2021

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 · August 2021

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 · August 2021

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 · August 2021

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

rf1234 · August 2021

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 · August 2021

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 · August 2021

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 · August 2021

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 · August 2021

Thank you a lot!

Have a good day

rf1234 · August 2021

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

lionellionel · August 2021

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

rf1234 · August 2021

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 · August 2021

WOW! It is a so completed answer!

Thanks a lot!

rf1234 · August 2021

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

Sort a column by date

Sort a column by date

Answers

Howdy, Stranger!

Categories

DataTables

Sort a column by date

Sort a column by date

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables