DataTables columnFilter numeric filtering

Question

I have a database where there is a date in the column "BIRTH_DATE" (for example, 2015-06-26 ). I use DataTables to display information for users. I want to search a range of numbers. But when I use the ColumnFilter plugin in ColumnFilter and try to use a filter like number-range , it does not work.

As soon as I enter any value in the from or to field, it tells me that there are no results. But if in the same column there is a row where the date is written like that, then filter 20150626 shows this. So, I understand that the problem is in the symbol - in the middle of my number. How can I ignore the filter - ?

Number Range Filter Code:

 function fnCreateCharRangeInput() { th.html(_fnRangeLabelPart(0)); var sFromId = sTableId + 'range_from_' + i; var from = $('<input type="text" class="number_range_filter" id="' + sFromId + '" rel="' + i + '"/>'); th.append(from); th.append(_fnRangeLabelPart(1)); var sToId = sTableId + 'range_to_' + i; var to = $('<input type="text" class="number_range_filter" id="' + sToId + '" rel="' + i + '"/>'); th.append(to); th.append(_fnRangeLabelPart(2)); th.wrapInner('<span class="filterColumn filter_number_range" />'); var index = i; aiCustomSearch_Indexes.push(i); //------------start range filtering function /* Custom filtering function which will filter data in column four between two values * Author: Allan Jardine, Modified by Jovan Popovic */ $.fn.dataTableExt.afnFiltering.push( function (oSettings, aData, iDataIndex) { var iMin = document.getElementById(sFromId).value * 1; var iMax = document.getElementById(sToId).value * 1; var iValue = aData[index] == "-" ? 0 : aData[index] * 1; if (iMin == "" && iMax == "") { return true; } else if (iMin == "" && iValue < iMax) { return true; } else if (iMin < iValue && "" == iMax) { return true; } else if (iMin < iValue && iValue < iMax) { return true; } return false; } ); //------------end range filtering function $('#' + sFromId + ',#' + sToId, th).keyup(function () { var iMin = document.getElementById(sFromId).value * 1; var iMax = document.getElementById(sToId).value * 1; if (iMin != 0 && iMax != 0 && iMin > iMax) return; oTable.fnDraw(); }); } 

EDIT: 2015-06-29

Or maybe someone can help me make this filter ignore the input format, just follow simple steps, like for example:

 Select * from table where BIRTH_DATE between '2010' and '2011-12' 

Because such a query works fine in sql.

Answer 1

I also ran into a problem:

There may be two reasons:

1) The value of iMin , iMax , iValue should be in seconds means number of milliseconds since 1970/01/01 this is getTime ()

eg,

 var iMin = document.getElementById(sFromId).value 

Because for comparison (iMin == "" && iValue < iMax) you use arithmetic operators ( =,<,> ), so the value of these three variables must be numeric.

2) Please confirm this first: I assume your date format is similar to this 2015-06-26 You need to convert the date to this 2015/06/26 format for it to work. Not sure why, but in some cases jquery doesn't accept 1970-01-01 and 1970/01/01 works fine.

Look at my function

 $.fn.dataTableExt.afnFiltering.push( function(oSettings, aData, iDataIndex) { if(chart.XminDate != '' && chart.XmaxDate != ''){ minDateFilter = new Date( chart.XminDate.replace(/\-/g,'/') ).getTime(); maxDateFilter = new Date( chart.XmaxDate.replace(/\-/g,'/') ).getTime(); aData._date = new Date( aData[3].replace(/\-/g,'/') ).getTime(); if (minDateFilter) { if (aData._date < minDateFilter) { return false; } } if (maxDateFilter) { if (aData._date > maxDateFilter) { return false; } } return true; } return true; } ); 

In my function

  var iMin = document.getElementById(sFromId).value * 1; /*same as chart.XminDate */ var iMax = document.getElementById(sToId).value * 1; /*same as chart.XmaxDate */ var iValue = aData[index] == "-" ? 0 : aData[index] * 1; /*same as aData._date */ 

In this line

  minDateFilter = new Date( chart.XminDate.replace(/\-/g,'/') ).getTime(); 

I replaced - with / after that I created a date object, and then I used the getTime() function to get the number of milliseconds since 1970/01/01

So, I think (since I have no idea about your HTML). This will help you.

Answer 2

What is the type of column BIRTH_DATE ?

My advice would be to make this datetime (something similar to 2015-06-26 16:10:18.820 , although it might also be without an exact hour).
Datatables can sort by datatime if you set the column type to date .

See here for a more detailed description of column.type in DataTables https://datatables.net/reference/option/columns.type

Answer 3

Instead of number-range try date-range . See an example here.

Answer 4

First, run the update in your database in the table to remove the “-” column in the birth_date column, if necessary.

Secondly, create a trigger in your database before inserting that formats birth_date varchar to the desired format.

You can then filter your initial number_range search into datatable .

But, as Mazet said, it would be better to use datetime for better query performance. If this is not possible, you have a quick way around the above.