Sort a column by date

Sort a column by date

lionellionellionellionel Posts: 21Questions: 4Answers: 0

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

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

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

    moment.js is case sensitive

  • lionellionellionellionel Posts: 21Questions: 4Answers: 0

    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.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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.

  • lionellionellionellionel Posts: 21Questions: 4Answers: 0
    edited 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?

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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"

  • lionellionellionellionel Posts: 21Questions: 4Answers: 0

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

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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.

  • lionellionellionellionel Posts: 21Questions: 4Answers: 0
    edited 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.

  • lionellionellionellionel Posts: 21Questions: 4Answers: 0

    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!

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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 );
    
  • lionellionellionellionel Posts: 21Questions: 4Answers: 0
    edited August 2021

    Thank you a lot!

    Have a good day :)

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

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

  • lionellionellionellionel Posts: 21Questions: 4Answers: 0
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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 );
        };
    };
     
    }));
    
  • lionellionellionellionel Posts: 21Questions: 4Answers: 0

    WOW! It is a so completed answer! :open_mouth:

    Thanks a lot!

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    no problem - we all run into the same issues at some point in time :smile:
    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

Sign In or Register to comment.