DataTables columnFilter number-range filtering

Question

I have database where in column "BIRTH_DATE" there is date (for example 2015-06-26). I use DataTables to display information for users. I want to make number range search. But when I am using DataTables plugin ColumnFilter and try to use number-range type filter it doesn't works.

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

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 somebody could help me to make this filter ignore input format just run simple action like for example:

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

Because query like that works fine in sql.

Answer 1

What is the type of your column BIRTH_DATE?

My advice would be to make it a datetime (something looking like 2015-06-26 16:10:18.820, although it can also be without the precise hour).
Datatables can sort by datatime if you set your column type to date.

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

Answer 2

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

Answer 3

I have also faced the problem:

There might be two reasons:

1) Value of iMin,iMax, iValue must be in seconds means in number of milliseconds since 1970/01/01 check this getTime()

eg

var iMin = document.getElementById(sFromId).value

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

2) Please confirm this first: I guess your date format is like this 2015-06-26 You have to convert date to this format 2015/06/26 to make it work. Not sure why but in some cases jquery doesn't accept 1970-01-01 and 1970/01/01 works perfectly.

Have a look on 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 have replaced - with / after this I have created a date object and then I have used getTime() function on it to get number of milliseconds since 1970/01/01

So I guess(as I have no idea about your HTML) This will help you.

Answer 4

First, run an update in your database to your table to delete the "-" in the birth_date column if needed.

Second, create a trigger in your database before insert that formats the BIRTH_DATE varchar to the desired format.

Then you will be able to filter with your initial number_range search in the datatable.

But, like Mazet said, it would be better to use datetime for the best query performance. If this is not possible, then you have my quick workaround above.