Multiple date range filters using jQuery DateRangePicker & Datatable

Question

What I would like to do is create multiple custom filters of different types on a jQuery Datatable.

As can be seen in this jsFiddle, I have successfully been able to create the input boxes per column. Some are text searches, others are date ranges. I have gotten the jQuery daterangepicker to work for those input boxes that have the class'date-filter'. If you click on Show/Hide columns and choose 'Date Created' or 'Date Updated' you will see the date picker.

I have created a custom filter that pushes the date criteria chosen. ($.fn.dataTableExt.afnFiltering.push(DateFilterFunction)). However, the filter does not work as expected. I want the filters to be cumulative. If you select a date range for one column (e.g. Date Updated) and then select for another column (e.g. DateCreated) the filter would seem to work. However, if you change the second filter, the other filter will change. Also, when clearing a particular filter on a date range column, all the existing date range column filters are removed. The behavior is erratic.

Can someone please look at the code and fix the issue.

   $(function () {


   $('#mytable thead tr.filters th').each(function () {
       var title = $(this).text();


       if ($(this).hasClass("input-filter")) {


           $(this).html('<input name ="' + $.trim(title).replace(/ /g, '') + '" type="text" class = "form-control" placeholder="Search ' + $.trim(title) + '" />');
       }
       else if ($(this).hasClass("date-filter")) {

           $(this).html('<div class="input-prepend input-group"><span class="add-on input-group-addon"><i class="glyphicon glyphicon-calendar fa fa-calendar"></i></span><input type="text" style="width: 200px" name="' + $.trim(title).replace(/ /g, '') + '"  placeholder="Search ' + $.trim(title) + '" class="form-control daterange"/></div>');

       }

   });


    // DataTable
    var table = $("#mytable").DataTable({

        dom: "rBftlip",

        buttons: [
        {
            extend: 'collection',
            text: 'Export',
            buttons:
                [

                    {
                        extend: "copy",
                        exportOptions: { columns: ':visible:not(:last-child)' }, //last column has the action types detail/edit/delete
                        footer:true
                    },
                    {
                        extend: "csv",
                        exportOptions: { columns: ':visible:not(:last-child)' },
                        footer: true
                    },
                    {
                        extend: "excel",
                        exportOptions: { columns: ':visible:not(:last-child)' },
                        footer:true
                    },
                    {
                        extend: "pdf",
                        exportOptions: { columns: ':visible:not(:last-child)' },
                        footer:true
                    },
                    {
                        extend: "print",
                        exportOptions: { columns: ':visible:not(:last-child)' },
                        footer: true
                    }

                ]
        }
        ],

        responsive: true,
        "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
        orderCellsTop: true,
        scrollX: true,
        colReorder: true,


        language: {
            search: '<div class="input-group"><span class="input-group-addon"><span class="glyphicon glyphicon-search"></span></span>',
            searchPlaceholder: 'Search all columns',
            lengthMenu: '<div class="input-group"><span class="input-group-addon"><span class="glyphicon glyphicon-menu-hamburger"></span></span>_MENU_</div>',
            processing: "<img src='../Content/images/ajax-loader.gif'>"
        },

        processing: true,


        "initComplete": function (settings, json) {
            //  $("#mytable_processing").css("visibility", "hidden");
            $('#mytable').fadeIn();
        },



        "footerCallback": function( tfoot, data, start, end, display ) {
            var info = $('#mytable').DataTable().page.info();
            $(tfoot).find('td').eq(0).html("Total Count: " + info.recordsDisplay);


        },

    });

    new $.fn.dataTable.Buttons(table, {
        buttons: [
          {
              extend: 'colvis',
              text: 'Show/Hide Columns'

          },

        ]
    });

    //add button to top
    table.buttons(0, null).container().prependTo(
          table.table().container()
      );


    //remove class from search filter
    ($("#mytable_filter input").removeClass("input-sm"));





    //instantiate datepicker and choose your format of the dates
    $('.daterange').daterangepicker({
        ranges: {
            "Today": [moment(), moment()],
            'Yesterday': [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
            '7 last days': [moment().subtract(6, 'days'), moment()],
            '30 last days': [moment().subtract(29, 'days'), moment()],
            'This month': [moment().startOf('month'), moment().endOf('month')],
            'Last month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
        },
        autoUpdateInput: false,
        opens:"left",
        locale: {
            cancelLabel: 'Clear',
            format: 'DD-MMM-YYYY'
        }
    });


   // $.fn.dataTableExt.afnFiltering = new Array();

   //get column index for date range
    var visiblecolumnIndex;
    var dataColumnIndex;  //current data column to work with

    $("#mytable_wrapper thead").on("mousedown", "th", function (event) {
        visiblecolumnIndex = $(this).parent().children().index($(this));
        dataColumnIndex = $("tr:first-child").children(':eq(' + visiblecolumnIndex + ')').attr('data-column-index');

    });


    var startDate;
    var endDate;



   var DateFilterFunction = (function (settings, data, iDataIndex) {

       var filterstart = startDate;
       var filterend = endDate; 
        var iStartDateCol = dataColumnIndex;
        var iEndDateCol = dataColumnIndex;

        var tabledatestart = data[iStartDateCol] !== "" ? moment(data[iStartDateCol], "DD-MMM-YYYY") : data[iStartDateCol];
        var tabledateend = data[iEndDateCol] !== "" ? moment(data[iEndDateCol], "DD-MMM-YYYY") : data[iEndDateCol];



        if (filterstart === "" && filterend === "") {
            return true;
        }

        else if ((moment(filterstart, "DD-MMM-YYYY").isSame(tabledatestart) || moment(filterstart, "DD-MMM-YYYY").isBefore(tabledatestart)) && filterend === "") {
            return true;
        }
        else if ((moment(filterstart, "DD-MMM-YYYY").isSame(tabledatestart) || moment(filterstart, "DD-MMM-YYYY").isAfter(tabledatestart)) && filterstart === "") {
            return true;
        }
        else if ((moment(filterstart, "DD-MMM-YYYY").isSame(tabledatestart) || moment(filterstart, "DD-MMM-YYYY").isBefore(tabledatestart)) && (moment(filterend, "DD-MMM-YYYY").isSame(tabledateend) || moment(filterend, "DD-MMM-YYYY").isAfter(tabledateend))) {
            return true;
        }

        return false;


    });




   $(".daterange", this).on('apply.daterangepicker', function (ev, picker) {
       ev.preventDefault();
       $(this).val(picker.startDate.format('DD-MMM-YYYY') + ' to ' + picker.endDate.format('DD-MMM-YYYY'));
       startDate = picker.startDate.format('DD-MMM-YYYY');
       endDate = picker.endDate.format('DD-MMM-YYYY');
       $.fn.dataTableExt.afnFiltering.push(DateFilterFunction);

       table.draw();

   });

   $(".daterange", this).on('cancel.daterangepicker', function (ev, picker) {
       ev.preventDefault();
       $(this).val('');
       startDate = '';
       endDate = '';
       $.fn.dataTableExt.afnFiltering.push(DateFilterFunction);

       table.draw();


   });



    //hide unnecessary columns
    var column = table.columns($('.HideColumn'));
    // Toggle the visibility
    column.visible(!column.visible());

    // Apply the search
    $.each($('.input-filter', table.table().header()), function () {
        var column = table.column($(this).index());
        //onsole.log(column);
        $('input', this).on('keyup change', function () {
            if (column.search() !== this.value) {
                column
                    .search(this.value)
                    .draw();
            }
        });
    });





}); /////////////////////// end of Datatable function

Answer 1

Coming late, but I got it working perfectly with the given below code:

Here is a snippet from my code where I have filtered age and date. You have just have to change it with your own data. Place this code after you have initialized your DataTable:

     $.fn.dataTable.ext.search.push(
        function( settings, data, dataIndex ) {
            var min = parseInt( $('#minAge').val());
            var max = parseInt( $('#maxAge').val());
            var age = parseFloat( data[3] ) || 0; // use data for the age column

            var dateMin = Date.parse($('#dateMin').val());
            var dateMax = Date.parse($('#dateMax').val());
            var date = parseFloat( Date.parse(data[4]) ) || 0; //use data for date column

            if ( (( isNaN( min ) && isNaN( max ) ) ||
                ( isNaN( min ) && age <= max ) ||
                ( min <= age   && isNaN( max ) ) ||
                ( min <= age   && age <= max )) && 
                (( isNaN(dateMin) && isNaN(dateMax)) || 
                ( isNaN(dateMin) && date <= dateMax) || 
                ( dateMin <= date && isNaN( dateMax )) ||
                ( dateMin <= date && date <= dateMax)) )
            {
                return true;
            }
                return false;
        }
    );

Now place a keyup trigger with jquery or anything on your input box where you gonna give the filtering

and put

table.draw();//where table is your initialized DataTable

and voila you are done!!

Answer 2

I got it to work eventually. Here was my solution for those interested:

https://jsfiddle.net/a9pLk0ud/2/

//instantiate datepicker and choose your format of the dates
    $('.daterange').daterangepicker({
        ranges: {
            "Today": [moment(), moment()],
            'Yesterday': [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
            '7 last days': [moment().subtract(6, 'days'), moment()],
            '30 last days': [moment().subtract(29, 'days'), moment()],
            'This month': [moment().startOf('month'), moment().endOf('month')],
            'Last month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')],
            'Blank date': [moment("0001-01-01"), moment("0001-01-01")]
        }
        ,
        autoUpdateInput: false,
        opens: "left",
        locale: {
            cancelLabel: 'Clear',
            format: 'DD-MMM-YYYY'
        }
    });

    var startDate;
    var endDate;
    var dataIdx;  //current data column to work with


    $("#mytable_wrapper thead").on("mousedown", "th", function (event) {
        var visIdx = $(this).parent().children().index($(this));
        dataIdx = table.column.index('fromVisible', visIdx);
    });




    // Function for converting a dd/mmm/yyyy date value into a numeric string for comparison (example 01-Dec-2010 becomes 20101201
    function parseDateValue(rawDate) {

        var d = moment(rawDate, "DD-MMM-YYYY").format('DD-MM-YYYY');
        var dateArray = d.split("-");
        var parsedDate = dateArray[2] + dateArray[1] + dateArray[0];
        return parsedDate;
    }





    //filter on daterange
    $(".daterange").on('apply.daterangepicker', function (ev, picker) {

        ev.preventDefault();



        //if blank date option was selected
        if ((picker.startDate.format('DD-MMM-YYYY') == "01-Jan-0001") && (picker.endDate.format('DD-MMM-YYYY')) == "01-Jan-0001") {
            $(this).val('Blank');


            val = "^$";

            table.column(dataIdx)
               .search(val, true, false, true)
               .draw();

        }
        else {
            //set field value
            $(this).val(picker.startDate.format('DD-MMM-YYYY') + ' to ' + picker.endDate.format('DD-MMM-YYYY'));



            //run date filter
            startDate = picker.startDate.format('DD-MMM-YYYY');
            endDate = picker.endDate.format('DD-MMM-YYYY');

            var dateStart = parseDateValue(startDate);
            var dateEnd = parseDateValue(endDate);

            var filteredData = table
                    .column(dataIdx)
                    .data()
                    .filter(function (value, index) {

                        var evalDate = value === "" ? 0 : parseDateValue(value);
                        if ((isNaN(dateStart) && isNaN(dateEnd)) || (evalDate >= dateStart && evalDate <= dateEnd)) {

                            return true;
                        }
                        return false;
                    });


            var val = "";
            for (var count = 0; count < filteredData.length; count++) {

                val += filteredData[count] + "|";
            }

            val = val.slice(0, -1);


            table.column(dataIdx)
                  .search(val ? "^" + val + "$" : "^" + "-" + "$", true, false, true)
                  .draw();
        }


       

    });


    $(".daterange").on('cancel.daterangepicker', function (ev, picker) {
        ev.preventDefault();
        $(this).val('');
        table.column(dataIdx)
              .search("")
              .draw();

     



    });