how to retrieve the data based on the difference between the start date and end date(2 columns)

vaishnavkoka

I am only able to retrieve the data based on the the date of single column, but i would like to get the result based on two columns. here is an example :http://live.datatables.net/sewehuwe/1/

it should be something like select * from tableName where startdate='somedate' and enddate='somedate';

colin

Hi @vaishnavkoka ,

If you're doing as a table search, you can just search on each of the columns individually with column().search(). If you just want the data, you would need to go through the rows with rows().every() and filter them out that way.

Cheers.

Colin

vaishnavkoka

Hi @colin ,
I want the data to be shown when i insert a date in minimum date field and maximum date field, i.e., i want to retrieve the data based on this two parameters. If you look at the above example it just shows the data for a single column only, which is no way helpful to me.

colin

Hi @vaishnavkoka ,

I understand, it sounds like you want to use a range type search, as with the example here. There's a plugin specifically for date ranges, see here.

Cheers,

Colin

vaishnavkoka

Hi @colin ,
I tried but i didnt get the output, i even changed the ids as mentioned in the page you suggested but still it doesnt work.
Here is the link:
http://live.datatables.net/vocivoji/40/edit

colin

Hi @vaishnavkoka ,

You really need to debug that script. If you look in the console you'll see many errors - things which are easily caught (for example, you have document.getElementById('fini') but no element with the ID fini.

Cheers,

Colin

vaishnavkoka

Hi @colin , oh yeah i forgot to change them, even after changing i am not getting the output.
Thanks,
Koka

colin

OK, could you link to your changed one, please.

vaishnavkoka

Oh yeah, here it is:
http://live.datatables.net/lawowito/1/edit
I am not sure where did i go wrong looking forward to get a solution from you.

colin

Hi @vaishnavkoka ,

I just tried that link you posted above and there are still console errors. I've removed those in this example here.

Try adding some debug into those if statements, and that may help to understand what's going on. If you have no joy, let me know, and I'll see I can poke some more tomorrow.

Cheers,

Colin

vaishnavkoka

Hi @colin ,

I tried and it always gets into the first "if" statement, as i have already explained the requirement would you mind helping me out with the solution ?

colin

Hi @vaishnavkoka ,

I just took another look, see here. The problem is that the dates are in different format, so they're being parsed incorrectly. As this will be your data, you need to ensure the format is consistent and what you want for your users, then adapt the code accordingly.

Cheers,

Colin

vaishnavkoka

Hi @colin ,
Though i changed the format still it didnt work.

allan

Can you give us a link to an updated test case please.

vaishnavkoka

Hi @alan,
Here is the link :
http://live.datatables.net/lawowito/3/edit
beside this date field i would like to use drop down list for "Position" separately, so when i select the dates and a value from a drop down list such as "Director" i should able to get the result on the datatable.
Thanks
Koka

tangerine

Your data has some invalid dates. For example, 07/092012.

vaishnavkoka

Hi @tangerine ,
http://live.datatables.net/zomemoya/1/edit
you can check now, i still did not the required results.can you help me solve this issue?

Thanks
Koka

tangerine

You really need to examine your data for consistent dd/mm/yyyy format.

<td>05/25/2018</td>
<td>06/26/2018</td>

vaishnavkoka

date is in mm/dd/yyyy format and i entered in the same format, on your saying i changed the date values
http://live.datatables.net/zomemoya/1/edit
and still it didnt work.
Can you tell me where did i go wrong ?

tangerine

So you are storing dates as mm/dd/yyyy, and trying to filter them using dd/mm/yyyy?

vaishnavkoka

no , i am storing dates as mm/dd/yyyy and trying to filter them using the same format as mm/dd/yyyy.
http://live.datatables.net/zomemoya/3/edit

tangerine

start date    dd/mm/yyyy
end date      dd/mm/yyyy

allan

I'm seeing that for the input too @tangerine. I think its a localisation thing since its a <input type="date"> element - with US locale it would show as mm/dd/yyyy for example.

There were errors on the page, such as loading jQuery twice, using a date range plug-in file that wasn't needed, etc. Tidying it up allow allows it to run: http://live.datatables.net/zomemoya/4/edit .

This bit is still wrong:

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

The input type="date" field will give you the date in ISO8601 format - i.e. YYYY-MM-DD. You need to change your conversation from the current style (which is UK, even although you are trying to use US formatting...) to match the ISO format.

Likewise your decoding of the US style dates from the table are attempting to do UK date decoding, which is obviously not what you want to do and I'm not clear why you'd split the string like that.

If you would like one of us (Colin or I) to get it working for you, priority support is available.

Allan