Cannot filter on date column with french format using advanced search

keytrap

Link to test case: https://live.datatables.net/jusidoke/16/edit
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
I can't figure out how to make the advanced search work with my date column which is formatted in french DD/MM/YYYY.
For example, try to set StartDate = 27/05/2024 (27th of may 2024).

keytrap

Okay I found that if I manually put the date with dashes instead of slashes it works.

But why doesn't it work with slashes?

samot80

I also have the same problems. The only way it works well is to use the date format YYYY-MM-DD instead of DD/MM/YYYY, which is the usual format in my country. Regards

columnDefs: [
{
targets: [5, 18],
render: function(data) {
return moment(data, 'YYYY-MM-DD').format('YYYY-MM-DD');
}
},
]

allan

I think you've hit on a bug for compatibility with DataTables 2 and its improved / altered date rendering I'm afraid. We can actually see it in this example as well, where the two date columns should be operated on as date columns, but are not detected as such. I'll look into it and post back.

Allan

allan

I've been looking into it more and without some horrible hacks, it can't actually be done with DataTables and SearchBuilder at the moment. I'm going to correct that for DataTables 2.1 which is going to have an overhaul of the type system and this will fit in with that. Apologies I don't have an immediate fix, but 2.1 is shooting up my priority order due to a couple of issues like this.

Allan

keytrap

Hello @allan thank you for your quick reply. For now I use my little "hack" it's not that important, and I'll wait for the 2.1 release.

// The hack is to set the following format
$.extend(true, $.fn.dataTable.DateTime, {
    defaults: {
        locale: 'fr',
        invalidDate: 'Date non valide',
        format: 'DD-MM-YYYY',
    }
});

keytrap

Ok so after futher investigations it seems that my little hack only works for date Equals! Whenever you try to set before, after etc... it starts to compare dates by the day number and not by the full date.

https://live.datatables.net/puzotofe/1/edit

First I try with the 27th of May with "Equals", it works.
Then i try "Before" and it starts to find all the dates before 28/XX/XXXX discarding the month and year.

keytrap

Finally I found out the solution ! (Tried with datatables v1 and v2).
I had to put type: 'moment-DD/MM/YYYY', this was the trick ! Everything works now.

v2 : https://live.datatables.net/jusidoke/39/edit
v1: https://live.datatables.net/puzotofe/2/edit