Datatable date range filter with Daterangepicker not working (rendered with Moment.js)

Imagchine27

Good morning,

I am trying to implement a custom filter for a DataTable which has two columns with different formats.

Data is getting retrieved by ajax from a database table.

The column "fecha" is a "Date" column in the database and "Momento del registro" a Timestamp.

When I render the dates in the Datable, I use this method:

Inside columnDefs (DataTable definition) I have this 2 codes:

"Fecha" column

{ 
                    "targets": 2,
                    "width":columnaAnchuraRestringida,
                    "render": function ( data, type, row ){
                        if ( type === 'display' || type === 'filter' ) {
                            var fechaFormateada = row["fecha_revision"];
                            return (moment(data).format(formatoFechaSinTiempo));   
                        }
                        return data;  
                    }  
                },

"Momento del registro" column

{ 
                    "targets": 6,
                    "width":columnaAnchuraRestringida,
                    "render": function ( data, type, row ){
                        if ( type === 'display' || type === 'filter' ) {
                            var fechaFormateada = row["momento_registro"];
                            return (moment(data).format(formatoFecha));   
                        }
                        return data;  
                    }  
                },

This code renders the date from the database table, which has always the english format (YYYY/MM/DD for "fecha" and YYYY/MM/DD (HH:mm:ss) for "momento del registro") and change the date format output in the datatable column depending on the language. I am using the DataTable Moment Plugin.

The web I am working on will be available in 3 languages: spanish, english and german. I use different date output formats for the languages in the datatable:

• Spanish and German
  -- Column "Fecha": I use the format "DD/MM/YYYY"
  -- Column "Momento del registro": I use the format "DD/MM/YYYY (HH:mm)"

• English
  -- Column "Fecha: I use the format "YYYY/MM/DD"
  -- Column "Momento del registro": I use the format "YYYY/MM/DD (HH:mm)"

In my "Filtro avanzado" (Advanced filter) section I have two DateRangePicker inputs.

What I am trying to do is to filter the column "Fecha" when I choose a range from the input "Fecha de revisión" and to filter the "Momento del registro" column when I pick a range from the input "Momento del registro".

**This is my code which adds a filter to Datatable for column 2 ("fecha") and column 6 ("momento del registro"). It also draws the table when the date ranges change. **

$("#revisiones_filtro_avanzado_fecha_revision").change(function () {
         $(idTabla).DataTable().draw()
    });
    
    $("#revisiones_filtro_avanzado_momento_registro").change(function () {
         $(idTabla).DataTable().draw()
    });
        
    $.fn.dataTableExt.afnFiltering.push(
        function( oSettings, aData, iDataIndex ) {

            var grab_daterange = $("#revisiones_filtro_avanzado_fecha_revision").val();
            var give_results_daterange = grab_daterange.split(" - ");
            var filterstart = give_results_daterange[0];
            var filterend = give_results_daterange[1];
            var iStartDateCol = 2;
            var iEndDateCol = 2;
            var tabledatestart = aData[iStartDateCol];
            var tabledateend= aData[iEndDateCol];

            if ( !filterstart && !filterend )
            {
                return true;
            }
            else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isBefore(tabledatestart)) && filterend === "")
            {
                return true;
            }
            else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isAfter(tabledatestart)) && filterstart === "")
            {
                return true;
            }
            else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isBefore(tabledatestart)) && (moment(filterend).isSame(tabledateend) || moment(filterend).isAfter(tabledateend)))
            {
                return true;
            }
            return false;
        }
    );

$.fn.dataTableExt.afnFiltering.push(
        function( oSettings, aData, iDataIndex ) {

            var grab_daterange = $("#revisiones_filtro_avanzado_momento_registro").val();
            var give_results_daterange = grab_daterange.split(" - ");
            var filterstart = give_results_daterange[0];
            var filterend = give_results_daterange[1];
            var iStartDateCol = 6;
            var iEndDateCol = 6;
            var tabledatestart = aData[iStartDateCol];
            var tabledateend= aData[iEndDateCol];

            if ( !filterstart && !filterend )
            {
                return true;
            }
            else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isBefore(tabledatestart)) && filterend === "")
            {
                return true;
            }
            else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isAfter(tabledatestart)) && filterstart === "")
            {
                return true;
            }
            else if ((moment(filterstart).isSame(tabledatestart) || moment(filterstart).isBefore(tabledatestart)) && (moment(filterend).isSame(tabledateend) || moment(filterend).isAfter(tabledateend)))
            {
                return true;
            }
            return false;
        }
    );

What is happening now

When I load the page in "english" and try to filter, only the "Momento del registro" filter works but Fecha" column is not being filtered properly (it returns 0 results).

When I load the page in spanish or german, not even the "Momento del registro" works.

What I need

How could I get make this filtering work for the 2 columns and different languages? See attached picture for reference.