Search between date & format error

m75sa

Hi,
i followed the script to filter datatable results by date range.

Results date format is dd/mm/YYYY (i.e. 17/09/2022)

when i filter from the picker it doesn't work. it only works when i change the result date format in Y-m-d
Any ideas how to fix?

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

m75sa

Hi colin,
this is the test case:

live.datatables.net/fotabebi/1/edit

kthorngren

Had to make a couple changes to get your test case to run. Your Datatables code is looking for 3 columns with the date in the 3rd column. But your table has only one column.

If you do some debugging you will find that var date = new Date( data[0] ); returns "Invalid Date". The Javascript Date() state the date is expected to by in ISO 8601 format. The ISO 8601 spec shows this as the format YYYY-MM-DDTHH:mm:ss.sssZ.

One option is to use moment.js to convert the date format. See the updated example:
http://live.datatables.net/jobewaji/1/edit

Kevin

m75sa

@kthorngren thanks a lot! This is what i needed.

m75sa

@kthorngren i'm reopening your help on this example. If i want to use ajax server side processing, how will work the data filter?

kthorngren

The search plugins only work with client side processing. With server side processing enabled you will need to send the date input values to the server. You can use ajax.data as a function to get the values and send them as parameters in addition to the normal server side processing parameters. See this example. The server script will need to grab these values and apply them as part of rthe data query for the date range.

Kevin

m75sa

@kthorngren...i need your help, as ever...
What i've done is:

in the html:

<input type="text" id="min" name="min">

in the js:

 $(document).ready(function() {


minDate = new DateTime($('#min'), {
         format: 'DD/MM/YYYY'
     });

     var table = $('#tblar').DataTable(
{
  
  "ajax":{
url :"<? echo $sezione; ?>-grid-data.php", // json datasource
data:function(dtParms){
dtParms.minDate = $('#min').val();
return dtParms
    },
type: "post",
error: function(){ 
$(".<? echo $sezione; ?>-grid-error").html("");
$("#<? echo $sezione; ?>-grid").append('<tbody class="<? echo $sezione; ?>-grid-error"><tr><th colspan="3">nothing found.</th></tr></tbody>');
$("#<? echo $sezione; ?>-grid_processing").css("display","none");
                            
},
            
 searchBuilder: { 
 columns: [1]
        },
     
   fixedColumns: true,
      stateSaveParams: function (settings, data) {
    delete data.search;
    
  },
},


processing: false,
serverSide: true,
responsive: true, "lengthChange": false, "autoWidth": false,
stateSave: true

});
  
    $('#min').on('change', function () {
                                      table.draw();
                                      });


table.column(0).visible(false);    
});

in the server side file:

$tbl = "cmd_data";

$sql = "SELECT * FROM " .$tbl;  
$query=mysqli_query($conn, $sql) or die($datagrid."-im-grid-data.php: get ".$datagrid."-im1");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData; 

$minDate = $_REQUEST['dtParms']['value'];
$sql = "SELECT * FROM (select * from " .$tbl. " where date(datarif) like '".$minDate."' )  as my_table_tmp ";   

but doesn't work...
where is the error?

Edited by Kevin: Syntax highlighting. Details on how to highlight code using markdown can be found in this guide

kthorngren

I'm not familiar with PHP but I would guess that this is wrong:

$minDate = $_REQUEST['dtParms']['value'];

You can do some debugging of what is sent in the request, ie look at the browser's network inspector for the XHR request parameters or in your PHP script, to see what parameters are recieved. But I believe you will want this:

$minDate = $_REQUEST['minDate'];

To match the parameter you add to the request here:

data:function(dtParms){
dtParms.minDate = $('#min').val();
return dtParms
    },

If you look at the example I link to there is no return statement. Remove return dtParms.

Kevin

m75sa

thanks! I fixed thanks to your help.

m75sa

@kthorngren
sorry... on the custom field i created (#min), when i set the savestate it doesn't save the status... how can i fix?

kthorngren

Since that is not a Datatables element but a custom element you created you will need to use stateSaveParams to add that value to the saved object. The stateLoadParams is used to get that saved object and restore it to the input value.

Kevin