Filtering between two same date but different time

Posted on December 23, 2012 at 11:56pm

I'm creating a jasper report which has the parameters of `datetrfrom` and `datetrto`.
The filtering goes well except when the the two parameters carry the same data such as

`datetrfrom>= '15-12-2012'`

`datetrto<= '15-12-2012'`

The data which are processed on the 15-12-2012 will not come out.

I tried this in my JSP :

function ShowReport(){
  var param='';              
 
  var timestart = ' 00:00:00'
  var timeend = ' 23:59:59'
 
  var datetrFrom = document.forms['report']['datetransfrom'].value;
  datetrFrom.split("-")
 
  //i split n join back the date to change the format from dd-MM-yyyy to yyyy-MM-dd
 
  var dtf = [datetrFrom[6],datetrFrom[7],datetrFrom[8],datetrFrom[9],datetrFrom[5],datetrFrom[3],datetrFrom[4],datetrFrom[2],datetrFrom[0],datetrFrom[1]].join('')
  datetrfr = dtf + timestart; //i join the date with the timestart to create format yyyy-MM-dd hh:mm:ss
 
  var datetrTo = document.forms['report']['datetransto'].value;
  datetrTo.split("-")
 
 
  var dtt = [datetrTo[6],datetrTo[7],datetrTo[8],datetrTo[9],datetrTo[5],datetrTo[3],datetrTo[4],datetrTo[2],datetrTo[0],datetrTo[1]].join('')
 
  datetrto = dtt + timeend; //i join the date with the timeend to create format yyyy-MM-dd hh:mm:ss
  param = param + '@@dtTFrom=='+datetrfr+'@@dtTTo=='+datetrto;


`@@dtTFrom` and `@@dtTTo` are the parameters that I use in my jasper report.

This is what I do in the where clause in my jasper report:

WHERE record_line_status='NO'
    AND datetime_process >= CONVERT(DATE, $P{dtTFrom},105)
    AND datetime_process <= CONVERT(DATE, $P{dtTTo},105)

But my report still does not work.. Can anyone help me?

I'm still new to jasper reports. I hope anyone can help me. Thank you in advance.

iReport Designer
mehan's picture
mehan
22
Joined: Dec 23 2012 - 11:47pm
Last seen: 10 years 3 months ago

2 Answers:

Posted on December 24, 2012 at 4:24am

why not try using java.sql.timestamp as parameter class.

spandey's picture
spandey
36
Joined: Dec 10 2012 - 12:59am
Last seen: 10 years 9 months ago

thank you @spandey.. I tried but it still does not work. however i solved it. thanks to you, you give idea to me.

mehan - 10 years 9 months ago
add comment
Posted on December 25, 2012 at 11:55pm

I have solved my problem here.
This is my new coding in my JSP:

function ShowReport(){
  var param='';              
 
    var timestart = ' 00:00:00'
 
    var timeend = ' 23:59:59'
 
    var orgFrom = document.forms['report']['org_id'].value;
    param = param + 'orgFrom=='+orgFrom;      
 
    var datetrFrom = document.forms['report']['datetransfrom'].value;
    var datefromsplit = datetrFrom.split('-');
    var datefromjoin = [datefromsplit[2], datefromsplit[1], datefromsplit[0]].join('-');
    var datefromsend = datefromjoin + timestart;
 
    var datetrTo = document.forms['report']['datetransto'].value;
    var datetosplit = datetrTo.split('-');
    var datetojoin = [datetosplit[2], datetosplit[1], datetosplit[0]].join('-');
    var datetosend = datetojoin + timeend;
 
    param = param + '@@dtTFrom=='+datefromsend+'@@dtTTo=='+datetosend;

and this is what I do in the where clause in my jasper report:

WHERE ep_lpp04_temp.record_line_status='No'
    AND EP_LPP04_TEMP.datetime_process >= CONVERT(datetime, $P{dtTFrom}, 120)
    AND EP_LPP04_TEMP.datetime_process <= CONVERT(datetime, $P{dtTTo}, 120)

by the way my parameter class is java.lang.string.

mehan's picture
mehan
22
Joined: Dec 23 2012 - 11:47pm
Last seen: 10 years 3 months ago
Feedback
randomness