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

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

vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

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';

Answers

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

    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.

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

    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

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

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

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

    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.

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

    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 ?

  • colincolin Posts: 15,118Questions: 1Answers: 2,583
    edited June 2018

    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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

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

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1
    edited July 2018

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

    <td>05/25/2018</td>
    <td>06/26/2018</td>
    
  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1

    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 ?

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

  • vaishnavkokavaishnavkoka Posts: 132Questions: 23Answers: 1
    edited July 2018

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    start date    dd/mm/yyyy
    end date      dd/mm/yyyy
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    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

This discussion has been closed.