Date range filter and Editor not working - no results when choosing date

asle

I am trying to get the date range filter example to work with Editor. When I choose a valid date I get no results. I wonder where I can look to troubleshoot this. I moved away from serverSide so now I get the complete json and can filter locally. There are only fewer than 5 thousand. records so it should be no problem speedwise.

I have my columns set up like this:

    columns: [ 
        { data: "garantinr" },
        { data: "forhandler" },
        { data: "kunde", render: function ( data, type, row ) {
            // Combine the first and last names into a single table field
            return row.kunde+' '+row.kunde_enavn;
        } },
        { data: "kunde_postnr"},            
        { data: "kunde_sted" },
        { data: "pumpemod" },
        { data: "varenummer" },
        { data: "mont_id" },
        { data: "reg_dato" },  //  ## this is the date field to sort ##
        { data: "ordrenummer", render: function ( data, type, row ) {
            if (row.ordrenummer == null) {return ''} else {
            return '<a href="https://spor.bring.no/sporing/' + 
            row.ordrenummer + '" target="–blank">' + 
            row.ordrenummer + '</a>'; 
            }
        } }, 
        { data: "kunde_enavn" },
    ],

columnDefs: [
        {
            targets: [8],
            render: $.fn.dataTable.render.moment('DD-MM-YYYY' ) // ## Show reg_dato in local date format ##
        }, 

I am not using serverSide since I need the smart search.
When I use the example function with the row containing date, I get "Uncaught ReferenceError: data8 is not defined". So I changed the function from function( settings, data, dataIndex ) { .... var date = new Date( data[8] ); to this below:

// Custom filtering function which will search data in column eight between two values
$.fn.dataTable.ext.search.push(
    function( data, type, row ) {
        var min = minDate.val();
        var max = maxDate.val();
        var date = new Date( row.reg_dato );
        if (
            ( min === null && max === null ) ||
            ( min === null && date <= max ) ||
            ( min <= date   && max === null ) ||
            ( min <= date   && date <= max )
        ) {
            return true;
        }
        return false;
    }
);

I create the date inputs with local date formatting but have also tried mySQL format YYYY-MM-DD

    // Create date inputs
    minDate = new DateTime($('#min'), {
        format: 'DD-MM-YYYY'
        //format: 'YYYY-MM-DD'
    });
    maxDate = new DateTime($('#max'), {
        format: 'DD-MM-YYYY'
        //format: 'YYYY-MM-DD'
    }); 
    // Refilter the table
    $('#min, #max').on('change', function ( settings, data, dataIndex ) {
        table.draw();   console.log($('#min').val());
   });

When I click in the startDate field and enter a date the table is redrawn but I get no records returned!

Here is some of my json

 "data": [
    {
      "DT_RowId": "row_3441",
      "id": "3441",
      "garantinr": "XXXXX-22-05-1",
      "ordrenummer": "618222",
      "kunde": "XXXXX",
      "kunde_enavn": "XXXXX",
      "kunde_adr": "XXXXX 39",
      "kunde_postnr": "4027",
      "kunde_sted": "Stavanger",
      "kunde_tel": "XXXXX",
      "pumpemod": "Lofoten Powerful",
      "varenummer": "601700-601701",
      "forhandler": "COOP OBS BYGG Madla",
      "ekstern_kom": "Ta kontakt med kunde..",
      "ekstern_reg": "1",
      "mont_id": "803548",
      "montdato": "2022-06-01",
      "rorlengde": "3",
      "testrun": "1",
      "trykktest_nitro": "1",
      "garanti_ok": "1",
      "reg_dato": "2022-05-18",
      "ferdig_status": "1"
    },
    {
      "DT_RowId": "row_3442",
      "id": "3442",
      "garantinr": "801214-22-05-1",
      "ordrenummer": "618276",
      "kunde": "Svein \XXXXX",
      "kunde_enavn": "XXXXX",
      "kunde_adr": "XXXXX 66",
      "kunde_postnr": "1927",
      "kunde_sted": "RXXXXX",
      "kunde_tel": "XXXXX",
      "pumpemod": "Narvik 25",
      "varenummer": "601702-601703",
      "forhandler": "COOP OBS BYGG XXXXX",
      "ekstern_kom": "Kundes telefon nummer: XXXXX\r\nmail",
      "ekstern_reg": "1",
      "mont_id": "801214",
      "montdato": "2022-06-13",
      "rorlengde": "6",
      "testrun": "ja",
      "trykktest_nitro": "1",
      "garanti_ok": "1",
      "reg_dato": "2022-05-18",
      "ferdig_status": "1"
    },
    {
      "DT_RowId": "row_3443",
      "id": "3443",
      "garantinr": "801214-22-05-2",
      "ordrenummer": "618276",
      "kunde": "Jon XXXXX",
      "kunde_enavn": "XXXXX",
      "kunde_adr": "XXXXX 68",
      "kunde_postnr": "1927",
      "kunde_sted": "XXXXX",
      "kunde_tel": "XXXXX",
      "pumpemod": "Narvik 25",
      "varenummer": "601702-601703",
      "forhandler": "COOP OBS BYGG XXXXX",
      "ekstern_kom": "Kunde har ikke oppgitt mail.",
      "ekstern_reg": "1",
      "mont_id": "801214",
      "montdato": "2022-06-13",
      "rorlengde": "5",
      "testrun": "ja",
      "trykktest_nitro": "1",
      "garanti_ok": "1",
      "reg_dato": "2022-05-18",
      "ferdig_status": "1"
    },
    {
      "DT_RowId": "row_3446",
      "id": "3446",
      "garantinr": "803548-22-05-2",
      "ordrenummer": "618223",
      "kunde": "XXXXX ",
      "kunde_enavn": "XXXXX",
      "kunde_adr": "\XXXXX 106",
      "kunde_postnr": "4330",
      "kunde_sted": "\XXXX\t",
      "kunde_tel": "XXXXX",
      "pumpemod": "Narvik 25",
      "varenummer": "601702-601703",
      "forhandler": "COOP OBS BYGG",
      "ekstern_kom": null,
      "ekstern_reg": "1",
      "mont_id": "803548",
      "montdato": "2022-06-01",
      "rorlengde": "2.7",
      "testrun": "1",
      "trykktest_nitro": "1",
      "garanti_ok": "1",
      "reg_dato": "2022-05-19",
      "ferdig_status": "1"
    },

I am sorry I can not show a working example since this is on a closed site. I can of course make a sample outside the site if necesarry.

asle

Hi, has anyone implemented the date range filter with Editor or is it only used in DataTables?

kthorngren

The Date Range filter works with Datatables whether you are using Editor or not. Datatables is in control of the table display while Editor is an add-on (for lack of a better word) to allow for editing the table.

The place to start is to do some debugging to find out what the result of var date = new Date( row.reg_dato ); is. Set a breakpoint and use the browser's debugger or use console.log(date);. My guess is you will find the same issue as this thread.

If you still need help please provide a test case so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

asle

Thanks I read the thread you referred to. But none of the test example pages work. I get "ERR_TOO_MANY_REDIRECTS" on all pages for "http://live.datatables.net/"

When I log the result of console.log(date) I get Invalid Date.
If I use the original example with the function function( settings, data, dataIndex ) { - I get a 500 server error. I know my field is data[8] but when I do console.log(row.reg_dato) I get undefined
It seems datatables can not find the correct column. This is strange. I can access the column with this code and it formats the date correctly:

columnDefs: [
        {
            targets: [8],  // this is the reg_dato field
            render: $.fn.dataTable.render.moment('DD-MM-YYYY' )
        }, 

kthorngren

I get "ERR_TOO_MANY_REDIRECTS" on all pages for "http://live.datatables.net/"

Hmm, strange, it works for me. Anyway here is the code from the working search plugin. Notise on line 6 its using moment.js to format the date to an ISO format. You will likely need to do the same be changing DD/MM/YYYY to the format you are using.

 // Custom filtering function which will search data in column four between two values
 $.fn.dataTable.ext.search.push(
     function( settings, data, dataIndex ) {
         var min = minDate.val();
         var max = maxDate.val();
         var date = new Date( moment(data[0], 'DD/MM/YYYY').format('YYYY-MM-DD') );
       
  
         if (
             ( min === null && max === null ) ||
             ( min === null && date <= max ) ||
             ( min <= date   && max === null ) ||
             ( min <= date   && date <= max )
         ) {
             return true;
         }
         return false;
     }
 );

Kevin