Novy
Novy

Reputation: 334

How do I filter date range in DataTables?

I have a large dataTable which contains ride information. Every row has a start datetime and an end datetime of the following format(yyyy-mm-dd HH:mm:ss). How can I use a datepicker for setting a filter range in dataTables? I want to have a datepicker which only selects a day and not the time. I've searched everywhere for the proper answer but I couldn't find it.

For example I want to see all rows of July by selecting (01-07-2016 - 31-07-2016).

Upvotes: 26

Views: 171934

Answers (7)

estinamir
estinamir

Reputation: 503

Using other posters code with some tweaks:

<table id="MainContent_tbFilterAsp" style="margin-top:-15px;">
        <tbody>
            <tr>
                <td style="vertical-align:initial;"><label for="datepicker_from" id="MainContent_datepicker_from_lbl" style="margin-top:7px;">From date:</label>&nbsp;
                </td>
                
                <td style="padding-right: 20px;"><input name="ctl00$MainContent$datepicker_from" type="text" id="datepicker_from" class="datepick form-control hasDatepicker" autocomplete="off" style="cursor:pointer; background-color: #FFFFFF">&nbsp;&nbsp;&nbsp;
                </td>
                
                <td style="vertical-align:initial"><label for="datepicker_to" id="MainContent_datepicker_to_lbl" style="margin-top:7px;">To date:</label>&nbsp;
                </td>
                
                <td style="padding-right: 20px;"><input name="ctl00$MainContent$datepicker_to" type="text" id="datepicker_to" class="datepick form-control hasDatepicker" autocomplete="off" style="cursor:pointer; background-color: #FFFFFF">&nbsp;&nbsp;&nbsp;                        
                </td>
                
                <td style="vertical-align:initial"><a onclick="$('#datepicker_from').val(''); $('#datepicker_to').val(''); return false;" id="datepicker_clear_lnk" style="margin-top:7px;">Clear</a></td>
            </tr>
        </tbody>
    </table>
    
    <script>
        $(document).ready(function() {
            $(function() {
                var oTable = $('#tbAD').DataTable({
                "oLanguage": {
                    "sSearch": "Filter Data"
                },
                "iDisplayLength": -1,
                "sPaginationType": "full_numbers",
                "pageLength": 50, 
                });

                $("#datepicker_from").datepicker();
                $("#datepicker_to").datepicker();

                $('#datepicker_from').change(function (e) {
                    oTable.draw();
                });
                $('#datepicker_to').change(function (e) {
                    oTable.draw();
                });
                $('#datepicker_clear_lnk').click(function (e) {
                    oTable.draw();
                });
            });   

            $.fn.dataTable.ext.search.push(
                function (settings, data, dataIndex) {
                var min = $('#datepicker_from').datepicker("getDate") == null ? null : $('#datepicker_from').datepicker("getDate").setHours(0,0,0,0);
                var max = $('#datepicker_to').datepicker("getDate") == null ? null : $('#datepicker_to').datepicker("getDate").setHours(0,0,0,0);
                var startDate = new Date(data[9]).setHours(0,0,0,0);
                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;
                }
            );
        });
</script>

Upvotes: 1

Jomal Johny
Jomal Johny

Reputation: 501

Here is my solution, there is no way to use momemt.js.Here is DataTable with Two DatePickers for DateRange (To and From) Filter.

$.fn.dataTable.ext.search.push(
  function (settings, data, dataIndex) {
    var min = $('#min').datepicker("getDate");
    var max = $('#max').datepicker("getDate");
    var startDate = new Date(data[4]);
    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;
  }
);
  

    

Upvotes: 1

FarhadRahimi
FarhadRahimi

Reputation: 41

 $.fn.dataTable.ext.search.push(
    function (settings, data, dataIndex) {
        var FilterStart = $('#filter_From').val();
        var FilterEnd = $('#filter_To').val();
        var DataTableStart = data[4].trim();
        var DataTableEnd = data[5].trim();
        if (FilterStart == '' || FilterEnd == '') {
            return true;
        }
        if (DataTableStart >= FilterStart && DataTableEnd <= FilterEnd)
        {
            return true;
        }
        else {
            return false;
        }
        
    });
    --------------------------
 $('#filter_From').change(function (e) {
        Table.draw();

    });
    $('#filter_To').change(function (e) {
          Table.draw();

    });

Upvotes: 4

Jothi Kannan
Jothi Kannan

Reputation: 3358

Following one is working fine with moments js 2.10 and above

$.fn.dataTableExt.afnFiltering.push(
        function( settings, data, dataIndex ) {
            var min  = $('#min-date').val()
            var max  = $('#max-date').val()
            var createdAt = data[0] || 0; // Our date column in the table
            //createdAt=createdAt.split(" ");
            var startDate   = moment(min, "DD/MM/YYYY");
            var endDate     = moment(max, "DD/MM/YYYY");
            var diffDate = moment(createdAt, "DD/MM/YYYY");
            //console.log(startDate);
            if (
              (min == "" || max == "") ||
              (diffDate.isBetween(startDate, endDate))


            ) {  return true;  }
            return false;

        }
    );

Upvotes: 0

mmushtaq
mmushtaq

Reputation: 3520

Here is DataTable with Single DatePicker as "from" Date Filter

LiveDemo

Here is DataTable with Two DatePickers for DateRange (To and From) Filter

LiveDemo

Upvotes: 45

CJ Edgerton
CJ Edgerton

Reputation: 420

Here is my solution, cobbled together from the range filter example in the datatables docs, and letting moment.js do the dirty work of comparing the dates.

HTML

<input 
    type="text" 
    id="min-date"
    class="date-range-filter"
    placeholder="From: yyyy-mm-dd">

<input 
    type="text" 
    id="max-date" 
    class="date-range-filter"
    placeholder="To: yyyy-mm-dd">

<table id="my-table">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Created At</th>
        </tr>
    </thead>
</table>

JS

Install Moment: npm install moment

// Assign moment to global namespace
window.moment = require('moment');

// Set up your table
table = $('#my-table').DataTable({
    // ... do your thing here.
});

// Extend dataTables search
$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
        var min  = $('#min-date').val();
        var max  = $('#max-date').val();
        var createdAt = data[2] || 0; // Our date column in the table

        if  ( 
                ( min == "" || max == "" )
                || 
                ( moment(createdAt).isSameOrAfter(min) && moment(createdAt).isSameOrBefore(max) ) 
            )
        {
            return true;
        }
        return false;
    }
);

// Re-draw the table when the a date range filter changes
$('.date-range-filter').change( function() {
    table.draw();
} );

JSFiddle Here

Notes

  • Using moment decouples the date comparison logic, and makes it easy to work with different formats. In my table, I used yyyy-mm-dd, but you could use mm/dd/yyyy as well. Be sure to reference moment's docs when parsing other formats, as you may need to modify what method you use.

Upvotes: 14

user5686312
user5686312

Reputation:

Follow the link below and configure it to what you need. Daterangepicker does it for you, very easily. :)

http://www.daterangepicker.com/#ex1

Upvotes: -1

Related Questions