Assistance with Datatables Date Range Filter using DatePicker?
Assistance with Datatables Date Range Filter using DatePicker?
richarro
Posts: 2Questions: 1Answers: 0
I have implemented Datatables which is working great using search bar and pagination, however, I've not been able to get the filter to work based on date ranges selected using Datepicker. I have thoroughly researched this forum but am unable to find any solutions that work in my case. My date(s) are in column 5 of the table. Dates are saved in the database as timestamps. Please help.
<!-- files needed for datatables installation -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="script.js"></script>
<!-- additional files needed for datatables styling -->
<script src="http://code.jquery.com/jquery-2.0.3.min.js" data-server="2.0.3" data-require="jquery"></script>
<script src="http://code.jquery.com/jquery-1.12.4.js" data-server="1.12.4" data-require="jquery"></script>
<link href="//cdnjs.cloudflare.com/ajax/libs/datatables/1.9.4/css/jquery.dataTables_themeroller.css" rel="stylesheet" data-server="1.9.4" data-require="datatables@*" />
<link href="//cdnjs.cloudflare.com/ajax/libs/datatables/1.9.4/css/jquery.dataTables.css" rel="stylesheet" data-server="1.9.4" data-require="datatables@*" />
<link href="//cdnjs.cloudflare.com/ajax/libs/datatables/1.9.4/css/demo_table_jui.css" rel="stylesheet" data-server="1.9.4" data-require="datatables@*" />
<link href="//cdnjs.cloudflare.com/ajax/libs/datatables/1.9.4/css/demo_table.css" rel="stylesheet" data-server="1.9.4" data-require="datatables@*" />
<link href="//cdnjs.cloudflare.com/ajax/libs/datatables/1.9.4/css/demo_page.css" rel="stylesheet" data-server="1.9.4" data-require="datatables@*" />
<link data-require="jqueryui@*" data-server="1.10.0" rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/jqueryui/1.10.0/css/smoothness/jquery-ui-1.10.0.custom.min.css" />
<script data-require="jqueryui@*" data-server="1.10.0" src="//cdnjs.cloudflare.com/ajax/libs/jqueryui/1.10.0/jquery-ui.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/datatables/1.9.4/jquery.dataTables.js" data-server="1.9.4" data-require="datatables@*"></script>
<body>
<div class="input-daterange">
<input type="text" id="min" name="min" class="form-control">
<span class="input-group-addon">to</span>
<input type="text" id="max" name="max" class="form-control">
</div>
<table class="user-table" id="datatable">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start date</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
<tr>
<td>leah</td>
<td>programmer</td>
<td>sprobe</td>
<td>25</td>
<td>2018-03-16 16:59:04</td>
<td>10000</td>
</tr>
<tr>
<td>aj</td>
<td>programmer</td>
<td>sprobe</td>
<td>24</td>
<td>2018-03-14 14:59:04</td>
<td>10000</td>
</tr>
<tr>
<td>jonald</td>
<td>data analyst</td>
<td>accenture</td>
<td>27</td>
<td>2018-04-04 19:05:29</td>
<td>10000</td>
</tr>
<tr>
<td>rey</td>
<td>programmer</td>
<td>elements</td>
<td>32</td>
<td>2018-04-04 19:05:29</td>
<td>10000</td>
</tr>
<tr>
<td>melanie</td>
<td>data analyst</td>
<td>accenture</td>
<td>26</td>
<td>2018-03-16 16:59:37</td>
<td>10000</td>
</tr>
<tr>
<td>ghelle</td>
<td>data analyst</td>
<td>accenture</td>
<td>25</td>
<td>2018-03-11 00:59:37</td>
<td>10000</td>
</tr>
</tbody>
</table>
</body>
</html>
// The plugin function for adding a new filtering routine
$.fn.dataTableExt.afnFiltering.push(
function(oSettings, aData, iDataIndex){
var dateStart = parseDateValue($("#min").val());
var dateEnd = parseDateValue($("#max").val());
// aData represents the table structure as an array of columns, so the script accesses the date value
// in the firth column of the table via aData[1]
var evalDate= parseDateValue(aData[4]);
if (evalDate >= dateStart && evalDate <= dateEnd) {
return true;
}
else {
return false;
}
});
$(document).ready(function(){
var oTable = $('#datatable').dataTable({
});
$('#min,#max').datepicker({
dateFormat: "yy-mm-dd",
showOn: "button",
buttonImageOnly: "true",
buttonImage: "datepicker.png",
weekStart: 1,
changeMonth: "true",
changeYear: "true",
daysOfWeekHighlighted: "0",
autoclose: true,
todayHighlight: true
});
// Add event listeners to the two range filtering inputs
$('#min,#max').change(function(){ oTable.fnDraw(); });
});
This discussion has been closed.
Answers
I have added a test case as requested, and have changed the js from what I initially had in attempts to get this working. I originally started using datatables in conjunction with datepicker. I reverted back to the example provided in this forum and am just not sure how to implement it for my test case.
http://live.datatables.net/xuruqoqa/1/