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

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 2

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