Datatables+datepicker Filter Table By Date Range

Trying to implement DataTables filter by date range using two bounded datepickers. I've seen few similar questions at SO, but none of those having answers worked for me. So far, I

Solution 1:

I made a few modifications to your code. See comments in the JavaScript snippet below, the bulk of which are at the bottom, under your var myDataTable = $('#staff').DataTable() declaration:

// I changed some of the start_date values to be more recent, just for testing.var myTableData = [{
    "id": "1",
    "name": "Tiger Nixon",
    "position": "System Architect",
    "salary": "$320,800",
    "start_date": "2017/04/25",
    "office": "Edinburgh",
    "extn": "5421"
  },
  {
    "id": "2",
    "name": "Garrett Winters",
    "position": "Accountant",
    "salary": "$170,750",
    "start_date": "2018/07/25",
    "office": "Tokyo",
    "extn": "8422"
  },
  {
    "id": "3",
    "name": "Ashton Cox",
    "position": "Junior Technical Author",
    "salary": "$86,000",
    "start_date": "2019/01/12",
    "office": "San Francisco",
    "extn": "1562"
  },
  {
    "id": "4",
    "name": "Cedric Kelly",
    "position": "Senior Javascript Developer",
    "salary": "$433,060",
    "start_date": "2018/03/29",
    "office": "Edinburgh",
    "extn": "6224"
  },
  {
    "id": "5",
    "name": "Airi Satou",
    "position": "Accountant",
    "salary": "$162,700",
    "start_date": "2018/11/28",
    "office": "Tokyo",
    "extn": "5407"
  },
  {
    "id": "6",
    "name": "Brielle Williamson",
    "position": "Integration Specialist",
    "salary": "$372,000",
    "start_date": "2018/12/02",
    "office": "New York",
    "extn": "4804"
  },
  {
    "id": "7",
    "name": "Herrod Chandler",
    "position": "Sales Assistant",
    "salary": "$137,500",
    "start_date": "2018/08/06",
    "office": "San Francisco",
    "extn": "9608"
  }
];

var myDataTable = $('#staff').DataTable({
  sDom: 't',
  data: myTableData,
  columns: [{
      title: 'Name',
      data: 'name'
    },
    {
      title: 'Position',
      data: 'position'
    },
    {
      title: 'Office',
      data: 'office'
    },
    {
      title: 'Hire date',
      data: 'start_date'
    },
    {
      title: 'Salary',
      data: 'salary'
    }
  ]
});

// I instantiate the two datepickers here, instead of all at once like before.// I also changed the dateFormat to match the format of the dates in the data table.
$("#startdate").datepicker({
  "dateFormat": "yy/mm/dd",
  "onSelect": function(date) {  // This handler kicks off the filtering.
    minDateFilter = newDate(date).getTime();
    myDataTable.draw(); // Redraw the table with the filtered data.
  }
}).keyup(function() {
  minDateFilter = newDate(this.value).getTime();
  myDataTable.draw();
});

$("#enddate").datepicker({
  "dateFormat": "yy/mm/dd",
  "onSelect": function(date) {
    maxDateFilter = newDate(date).getTime();
    myDataTable.draw();
  }
}).keyup(function() {
  maxDateFilter = newDate(this.value).getTime();
  myDataTable.draw();
});

// The below code actually does the date filtering.
minDateFilter = "";
maxDateFilter = "";

$.fn.dataTableExt.afnFiltering.push(
  function(oSettings, aData, iDataIndex) {
    if (typeof aData._date == 'undefined') {
      aData._date = newDate(aData[3]).getTime(); // Your date column is 3, hence aData[3].
    }

    if (minDateFilter && !isNaN(minDateFilter)) {
      if (aData._date < minDateFilter) {
        returnfalse;
      }
    }

    if (maxDateFilter && !isNaN(maxDateFilter)) {
      if (aData._date > maxDateFilter) {
        returnfalse;
      }
    }

    returntrue;
  }
);

<linkrel="stylesheet"type="text/css"href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css"><linkrel="stylesheet"type="text/css"href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"><scripttype="application/javascript"src="https://code.jquery.com/jquery-3.3.1.min.js"></script><scripttype="application/javascript"src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script><scripttype="application/javascript"src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script><label>Start date:</label><inputid="startdate"class="date_range_filter" /><label>End date:</label><inputid="enddate"class="date_range_filter" /><tableid="staff"></table>

I also removed the "Filter" button, as it's not needed in this implementation. The table should automatically update when you select a date from the datepickers.

Solution 2:

You have to add this code part to take date range and hire date.

    $(document).ready(function(){
     $.fn.dataTable.ext.search.push(
     function (settings, data, dataIndex) {
        var min = $('#startdate').datepicker("getDate");
        var max = $('#enddate').datepicker("getDate");
        var hireDate = newDate(data[3]);
        if (min == null && max == null) { returntrue; }
        if (min == null && hireDate <= max) { returntrue;}
        if(max == null && hireDate >= min) {returntrue;}
        if (hireDate <= max && hireDate >= min) { returntrue; }
        returnfalse;
    }
    );
    });

and you must click event for your button.

$("#filter").click(function () {

    var myDataTable = $('#staff').DataTable();
        myDataTable.draw();
});

First code part to get dates , second part to draw datatable again.