datatables date filter

Question

I have one Date column, formatted '17/03/2012'.

I would like to be able select a start and end date and if the 1 date column above is within this date range it will filter the column.

Below is the code im using:

        Start Date: <input type="text" id="dateStart" name="dateStart" size="30">
        End Date: <input type="text" id="dateend" name="dateend" size="30">

    <script type="text/javascript" charset="utf-8">

        $.fn.dataTableExt.afnFiltering.push(
            function( oSettings, aData, iDataIndex ) {
                var iFini = document.getElementById('dateStart').value;
                var iFfin = document.getElementById('dateend').value;
                var iStartDateCol = 2;
                var iEndDateCol = 2;

    iFini=iFini.substring(0,2) + iFini.substring(3,5)+ iFini.substring(6,10)
    iFfin=iFfin.substring(0,2) + iFfin.substring(3,5)+ iFfin.substring(6,10)       

    var datofini=aData[iStartDateCol].substring(0,2) + aData[iStartDateCol].substring(3,5)+ aData[iStartDateCol].substring(6,10);
    var datoffin=aData[iEndDateCol].substring(0,2) + aData[iEndDateCol].substring(3,5)+ aData[iEndDateCol].substring(6,10);


                if ( iFini == "" && iFfin == "" )
                {
                    return true;
                }
                else if ( iFini <= datofini && iFfin == "")
                {
                    return true;
                }
                else if ( iFfin >= datoffin && iFini == "")
                {
                    return true;
                }
                else if (iFini <= datofini && iFfin >= datoffin)
                {
                    return true;
                }
                return false;
            }
        );

$(function() {
    // Implements the dataTables plugin on the HTML table
    var $oTable= $("#example").dataTable( {
    "sDom": '<"top"><"clear">t<"bottom"i><"clear">',
        "iDisplayLength": 20,       
        "oLanguage": {
            "sLengthMenu": 'Show <select><option value="25">25</option><option value="50">50</option><option value="100">100</option><option value="200">200</option></select>'
        },
        "bJQueryUI": true,
        //"sPaginationType": "full_numbers",
        "aoColumns": [
                null,
                null,
                  { "sType": "date" }
        ]                    
        });    


    $('#dateStart, #dateend').daterangepicker(
        {
        dateFormat: 'dd/mm/yy',
        arrows: true
    }

    );        


    /* Add event listeners to the two range filtering inputs */
    $('#dateStart').keyup( function() { oTable.fnDraw(); } );
    $('#dateend').keyup( function() { oTable.fnDraw(); } );

    /* Add event listeners to the two range filtering inputs */
    $('#dateStart').change( function() { oTable.fnDraw(); } );
    $('#dateend').change( function() { oTable.fnDraw(); } );

    /* Add event listeners to the two range filtering inputs */
    $('#name').keyup( function() { oTable.fnDraw(); } );
    $('#name').change( function() { oTable.fnDraw(); } );
});

    </script>

Any help advice on this would bve extremely helpful. Thanks in advance.

mbeasley · Accepted Answer · 2012-03-19 16:59:54Z

6

I think the example that's shown in the filter API page will do the trick:

$(document).ready(function() {
    var oTable = $('#example').dataTable();

    /* Add event listeners to the two range filtering inputs */
    $('#min').keyup( function() { oTable.fnDraw(); } );
    $('#max').keyup( function() { oTable.fnDraw(); } );
} );

What the range filtering extension you've included up above is looking for is a set of input boxes (probably datepicker style textboxes would work best). You should give them the ID's, by what I see in your code, dateStart and dateend. Then you can bind function() { oTable.fnDraw(); } to some event on either of those boxes ( like in the code above, they're bound to the keyup event) or it could be a filter button or whatever.

But now, each time that the table is drawn (using fnDraw()) it will take into account those dates and filter your zero-based iStartDateCol and iEndDateCol columns based on that range.

UPDATE: a more direct answer - just include the following in your document.ready function:

$('#dateStart').keyup( function() { oTable.fnDraw(); } );
$('#dateend').keyup( function() { oTable.fnDraw(); } );

answered Mar 19, 2012 at 16:59

mbeasley

4,8745 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Codded Over a year ago

I already have this within my code, but it still does not work. I have updated my question with the full code.

mbeasley Over a year ago

@Codded So I think the problem is with date format / parsing. The extension is manually parsing the date string with the two lines that look like iFini=iFini.substring(6,10) + iFini.substring(3,5)+ iFini.substring(0,2). From what he has there, it looks like he's expecting something in MM/DD/YYYY format. I recommend you change the extension to something less rigid, maybe an actual parser that can handle multiple formats like the one you're describing. I think the native javascript date object has a parse method built in and if you're using jQueryUI at all - their datepicker does too.

Codded Over a year ago

Modified my answer again for date and ifini etc. The filter is working but the table only redraws itself when i click to sort a column. Its not initiating the draw when i have selected the date. Im using the daterangepicker plugin.

mbeasley Over a year ago

@Codded It would seem that your listeners aren't firing then. Two ideas: First - you can set a function for the onChange: function option in the daterangepicker, putting your table redraw in this section. I would put a console.log('redraw'); or something in the "fnDrawCallback": function(oSettings) {} option for the table to see when it's redrawing. The second thing I would consider if the above doesn't work is using live or on in your listeners.

Codded Over a year ago

Sorry I found a simple answer, was missing the $ from the start of oTable - $('#dateStart').keyup( function() { $oTable.fnDraw(); } ); all working now. Thanks for the help, much appreciated!

Hemant · Accepted Answer · 2013-12-19 08:42:59Z

1

If you want to filter a DataTable based on a date-range, you can try this function :

https://github.com/hemantrai88/datatables-date_range_filter

It is really simple to customize this function to make it work for different date-formats.

answered Dec 19, 2013 at 8:42

Hemant

1091 silver badge8 bronze badges

1 Comment

Jubayer Arefin Over a year ago

Thank you for the reference, it helped. I have forked and made some optimisation to the code.

Daria · Accepted Answer · 2016-10-31 14:17:14Z

I found a solution without using any plugin (I used this also to filter table by a keyword)

function filterTableByDateRange(table) {

    var id = table.attr("id");
// I added class dt to a date-column of table 
    var dates = ($('#' + id + ' td.dt').map(function () {
        return new Date($(this).text());
    }).get());

//Here we init min and max date to be filtered with, if start date or end date is unset we set it to min and max existing dates of our table respectively
    var minSearchDate = $('#date_search_from').val()
        ? new Date($('#date_search_from').val())
        : new Date(Math.min.apply(null, dates));

    var maxSearchDate = $('#date_search_to').val()
        ? new Date($('#date_search_to').val())
        : new Date(Math.max.apply(null, dates));

    var allRows = $("#" + id + " tbody").find("tr");
    if (this.value == "") {
        allRows.show();
        return;
    }

    allRows.hide();

    allRows.filter(function (i, v) {
        var currDate = new Date($(this).find(".dt").html());
        if (currDate.setHours(0, 0, 0, 0) >= minSearchDate.setHours(0, 0, 0, 0) &&
            currDate.setHours(0, 0, 0, 0) <= maxSearchDate.setHours(0, 0, 0, 0)) {
            return true;
        }
        return false;
    }).show();
}

Collectives™ on Stack Overflow

datatables date filter

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related