locked
How to fix data range filter in table? RRS feed

  • Question

  • User414040641 posted

    I'm using a script to filter dates between start/end dates but it won't do anything in my table.

                <script>
                    $(document).ready(function () {
    
                        $(function () {
                            var start = moment("2019-10-01 00:00:00");
                            var end = moment("2019-10-31 23:59:59");
    
                            function cb(start, end) {
                                $('#reportrange span').html(start.format('MMMM D, YYYY') + ' - ' + end.format('MMMM D, YYYY'));
                            }
    
                            $('#reportrange').daterangepicker({
                                startDate: start,
                                endDate: end,
                                ranges: {
    
                                }
                            }, cb);
    
                            cb(start, end);
    
                        });
    
    
                        $('#reportrange').on('apply.daterangepicker', function (ev, picker) {
                            var start = picker.startDate.format('YYYY-MM-DD');
                            var end = picker.endDate.format('YYYY-MM-DD');
    
    
                            console.log("-----------------------------");
    
                            $.fn.dataTable.ext.search.push(
                                function (settings, data, dataIndex) {
                                    var min = new Date(start);
                                    var max = new Date(end);
                                    var startDate = new Date(data[1]);
                                    console.log(startDate + " <= " + max + " --- " + (startDate <= max));
    
                                    if (min == null && max == null) {
                                        return true;
                                    }
                                    if (min == null && startDate <= max) {
                                        return true;
                                    }
                                    if (max == null && startDate >= min) {
                                        return true;
                                    }
                                    if (startDate <= max && startDate >= min) {
                                        return true;
                                    }
                                    return false;
                                }
                            );
                            table.draw();
                            $.fn.dataTable.ext.search.pop();
                        });
    
                    });
    
                </script>

    And this is the table, I expect it to filter the dates with start & end date but it doesn't do anything at all

                    <div class="card-body">
                                    <div id="tablas">
                                        <div id="var receive">
                                            <div class="table-striped">
                                                <table class="table table-bordered" width="100%" cellspacing="0">
                                                    <thead align="center">
                                                        <tr>
                                                            <th>A</th>
                                                            <th>B</th>
                                                            <th>C</th>
                                                            <th>D</th>
                                                            <th>E</th>
                                                            <th>Date</th>
                                                        </tr>
                                                    </thead>
                                                    <tbody id="mydataTable">
                                                        <tr>
                                                            <td align="center">1</td>
                                                            <td>987654 UserName</td>
                                                            <td align="center">Coins</td>
                                                            <td align="right">$1,000.00</td>
                                                            <td align="center">07/16/19</td>
                                                            <td align="center">
                                                                <form action="~/Index"> <button type="submit" class="btn btn-info">Select</button></form>
                                                            </td>
                                                        </tr>
    
    
                                                        <tr>
                                                            <td align="center">2</td>
                                                            <td>123456 UserName</td>
                                                            <td align="center">Coins</td>
                                                            <td align="right">$1,000.00</td>
                                                            <td align="center">10/16/19</td>
                                                            <td align="center">
                                                                <form action="~/Index"> <button type="submit" class="btn btn-info">Select</button></form>
                                                            </td>
                                                        </tr>
    
    
    
    
                                                    </tbody>
                                                </table>
                                            </div>
                                        </div>
                                    </div>
                                </div>
    
    

    I took this example from this fiddle looking up online for answers, since my table is renderized on the go and there's no sql connection well all the examples online won't help me doing this :(

    Reportrange goes just above the table it's a buttom

                <div class="col-lg-3 col-md-3 form-group">
                    <label>Buscar por fecha:</label>
                    <div class="input-group">
                        <div id="reportrange" class="btn btn-info btn-lg">
                            <span class="input-group-append">
                            </span> <b class="caret"></b>
                        </div>
                    </div>
                </div>

    Friday, November 1, 2019 12:22 AM

Answers

  • User-719153870 posted

    Hi napalm911,

    I'm using a script to filter dates between start/end dates but it won't do anything in my table.

    I think the problem is caused because you set the wrong column of your table as the date column, you need to specific which column you want to aplly the date range filter.

    Please refer to below code to update yours:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.dataTables.min.css" />
        <link rel="stylesheet" type="text/css" href="https://app.dineandgift.com/plugins/daterangepicker/daterangepicker.css" />
        <link rel="stylesheet" type="text/css" href="https://app.dineandgift.com/plugins/daterangepicker/daterangepicker.css" />
        <link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
        <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-daterangepicker/2.1.27/daterangepicker.css" />
        <script type="text/javascript" charset="utf8" src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
        <script type="text/javascript" charset="utf8" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
        <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.16.0/moment.min.js"></script>
        <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
        <script type="text/javascript" charset="utf8" src="https://app.dineandgift.com/plugins/daterangepicker/daterangepicker.js"></script>
        <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-daterangepicker/2.1.27/daterangepicker.js"></script>
        <script>
            $(document).ready(function () {
                $(function () {
                    var start = moment("2019-10-01 00:00:00");
                    var end = moment("2019-10-31 23:59:59");
    
                    function cb(start, end) {
                        $('#reportrange span').html(start.format('MMMM D, YYYY') + ' - ' + end.format('MMMM D, YYYY'));
                    }
    
                    $('#reportrange').daterangepicker({
                        startDate: start,
                        endDate: end,
                        ranges: {
    
                        }
                    }, cb);
    
                    cb(start, end);
    
                });
    
                $('#reportrange').on('apply.daterangepicker', function (ev, picker) {
                    var start = picker.startDate.format('YYYY-MM-DD');
                    var end = picker.endDate.format('YYYY-MM-DD');
    
    
                    console.log("-----------------------------");
    
                    $.fn.dataTable.ext.search.push(
                        function (settings, data, dataIndex) {
                            var min = new Date(start);
                            var max = new Date(end);
                            var startDate = new Date(data[4]);
                            console.log(startDate + " <= " + max + " --- " + (startDate <= max));
    
                            if (min == null && max == null) {
                                return true;
                            }
                            if (min == null && startDate <= max) {
                                return true;
                            }
                            if (max == null && startDate >= min) {
                                return true;
                            }
                            if (startDate <= max && startDate >= min) {
                                return true;
                            }
                            return false;
                        }
                    );
                    table.draw();
                    $.fn.dataTable.ext.search.pop();
                });
    
                var table = $('#example').DataTable({
                    "order": [
                        [0, "desc"]
                    ],
                    lengthChange: false
                });
            });
        </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div class="card-body">
                <div class="col-lg-3 col-md-3 form-group">
                    <label>Buscar por fecha:</label>
                    <div class="input-group">
                        <div id="reportrange" class="btn btn-info btn-lg">
                            <span class="input-group-append"></span><b class="caret"></b>
                        </div>
                    </div>
                </div>
                <div id="tablas">
                    <div id="var receive">
                        <div class="table-striped">
                            <table id="example" class="table table-bordered" width="100%" cellspacing="0">
                                <thead align="center">
                                    <tr>
                                        <th>A</th>
                                        <th>B</th>
                                        <th>C</th>
                                        <th>D</th>
                                        <th>E</th>
                                        <th>Date</th>
                                    </tr>
                                </thead>
                                <tbody id="mydataTable">
                                    <tr>
                                        <td align="center">1</td>
                                        <td>987654 UserName</td>
                                        <td align="center">Coins</td>
                                        <td align="right">$1,000.00</td>
                                        <td align="center">07/16/19</td>
                                        <td align="center">
                                            <form action="~/Index">
                                                <button type="submit" class="btn btn-info">Select</button>
                                            </form>
                                        </td>
                                    </tr>
    
    
                                    <tr>
                                        <td align="center">2</td>
                                        <td>123456 UserName</td>
                                        <td align="center">Coins</td>
                                        <td align="right">$1,000.00</td>
                                        <td align="center">10/16/19</td>
                                        <td align="center">
                                            <form action="~/Index">
                                                <button type="submit" class="btn btn-info">Select</button>
                                            </form>
                                        </td>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </form>
    </body>
    </html>

    Below is the result:

    Hope it could help.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 4, 2019 3:17 AM