Bilal Bilal - 15 days ago 5
Javascript Question

How to filter jquery data table by date column?

I have a data table that I am trying to filter based on the date in one column. I would like to filter the data based on the lastModified column having a date one year or older but even getting it filter on some hard coded date would be a good start. The data in in string format so I am trying to use the new Date() function to convert to date.

var table = $('#database').DataTable( {
fixedHeader: true,

data: dataSet,
columns: [
{ data: "processName" },
{ data: "processLob" },
{ data: "processOwner"},
{ data: "RiskReviewer"},
{ data: "lastModified"}]
} );

var filteredData = table
.column( { data: "lastModified"} )
.data()
.filter( function ( value, index ) {
return new Date(value) < 2015-10-10 ? true : false;
} );

Answer

First thing you are going to want to do is add "columnDefs" object for your date column and specify it's type as "date". DateTables has built in date parsing as long as you are following a well known format. ColumnType API Def

If that doesn't get you there completely then you will want to define a render function for your data column on the new columnDef object you just created. There you can check the render type and return a "nice" value for display and raw data value (ideally a value of type Date) for everything else. Render API Defintion

Also some general advice don't try to fight the library. It actually is extremely flexible and can handle a lot. So use the built in API functions where ever possible. Usually things go awry when people try to manipulate the table manually using JQuery. Under the covers the DataTables plugin maintains a ton of state that never makes it to the DOM. Basically if there is a function in the API for it, use it.

EDIT: Adding answer to the original posters question even though he found another solution.

One thing to keep in mind is that "filter" is intended only to give you back a filtered data set. It will not change the display in the grid. Instead you will want to use "search" on the "column()" API item to filter the displayed rows in the DataTable.

There is a small problem with that however. The search method only accepts regular values not functions. So if you want to implement this you have supply a custom search function like so:

// The $.fn.dataTable.ext.search array is shared amongst all DataTables and 
//  all columns and search filters are evaluated in the order in which they 
//  appear in 
//  the array until a boolean value is returned.  
$.fn.dataTable.ext.search.unshift(function(settings, data, dataIndex) {
     // Using a negative value to get the column wraps around to the end of 
     // the columns so "-1" will always be your last column.
     var dateColumn = $(this).column(-1);

     // We get the data index of the dateColumn and compare it to the index
     // for the column currently being searched.
     if(dateColumn.index() !== dataIndex) {'
        // Pretty sure this indicates to skip this search filter
        return null;
     }

     var columnSearchingBy = $(this).column(dataIndex);

     // Allows the data to be a string, milliseconds, UTC string format ..etc
     var columnCellData = new Date(data.lastModified);
     var valueToSearchBy = new Date(columnSearchingBy.search.value);

     // Ok this is one of the worst named methods in all of javascript.   
     // Doesn't actually return a meaningful time.  Instead it returns the a 
     // numeric value for the number of milliseconds since ~ 1970 I think.  
     //
     // Kind of like "ticks()" does in other languages except ticks are 
     // measured differently.  The search filter I am applying here is to 
     // only show dates in the DataTable that have a lastModified after or 
     // equal the column search.
     return (valueToSearchBy.getTime() >= columnCellData.getTime());
});


// So this should use our fancy new search function applied to our datetime
// column.  This will filter the displayed values in the DataTable and from 
// that just a small filter on the table to get all the data for the rows 
// that satisfy the search filter.
var filteredData = table
        .column({ data: "lastModified"})
        .search('2015-10-10')
        .draw();

Even though you found another way to go on this one maybe the above will help you out later on.