Greater than date as the second filterable value

silkspin

I need to find the same values in one column, then in a second column get the instances greater than a specified date. For example > 31/12/2019. It's the total number of instances that I need which is why I'm using count(). I can do this fine with a non-date value using the code below.

      var filteredData = datatable
        .rows()
        .eq(0)
        .filter(function(rowIdx) {
          return datatable.cell(rowIdx, 2).data() === "value A" && datatable.cell(rowIdx, 0).data() === "value B" ? true : false;
        });
      console.log(datatable.rows(filteredData).count());

I'm guessing my dates are classed as a string so the formatting is incorrect for filtering. I already use datetime-moment.js and moment.js. I added $.fn.dataTable.moment("DD/MM/YYYY"); to my js so the dates sort in the correct order.

Can anyone help by changing the code above to something that would work? It's the part after && I want to be the date. Thanks.

kthorngren

I would suggest using moment.js for your comparison. To give a more specific answer please build a simple test case that replicates your data so we can help you with your code.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

silkspin

Thanks Kevin. I've created a test http://live.datatables.net/leyozezi/1/edit?js,console,output

The first search result (officePosition) in the console shows the successfully filtering of "Directors in Edinburgh: 2".

The second search (officeDate) actually does show the correct result in this case, for "Starters in Edinburgh from 2012: 5". It seems this is because the format of the date in the demo test is YYYY/MM/DD. Unless the demo already has some other type of formatting applied to dates and therefore isn't a string?

My date format is DD/MM/YYYY and I had already formatted it $.fn.dataTable.moment("DD/MM/YYYY"); after including the moment.js. This was to allow the correct sorting. It would still be good to know how this can be achieved when filtering as a secondary value. Thanks.

kthorngren

It seems this is because the format of the date in the demo test is YYYY/MM/DD.

This is why I asked for your specific data. But you could try using the moment function like this:
table.cell(rowIdx, 4).data() > moment("2011/12/31", "YYYY/MM/DD")

Try this with your datetime format. If you still need help please update the test case (you can remove rows) to show you dates and any formatting you are doing with them.

Kevin

silkspin

Firstly I've created a new test case with the date format DD/MM/YYYY here http://live.datatables.net/cuxulala/1/edit?html,js,console,output.

I've added a link to moment.js from a CDN and tried table.cell(rowIdx, 2).data() > moment("31/12/2010", "DD/MM/YYYY") but that didn't show the expect answer of 3. It showed 0. Thanks.

kthorngren

I little debugging shows the date in the column data is a string. Use the moment function with it too.
http://live.datatables.net/cuxulala/2/edit

Kevin

silkspin

That's fantastic Kevin! Thank you so much. It works perfectly!