Read two parameters in BETWEEN of querySting?

[2005 IR Help]

By: Javat - javat

Read two parameters in BETWEEN of querySting?

2004-07-20 03:16

Hello,

 

I have a strange problem when editing my jasperreports file.

 

I have the following line in my queryString:

 

....

and A1.BRL_CREATION_DATE

between '$P!{BRL_CREATION_DATE}'

and '$P!{BRL_END_DATE}'

...

 

These parameters are defined as java.util.Date types. When i want to run this report with the BRL_END_DATE parameter set to ''2005-01-01', i get an empty report, but when i replace the second parameter with '2005-01-01', i do get a report with rows.

 

Does jasperreports have a problem reading two parameters for one sql line?

 

I don't see the solution, because the only thing i've changed is the reading of a parameter.

 

Please help me out, i've spend all morning looking for the solution on this forum.

 

 

 

 

By: jadawinrules - jadawinrules

RE: Read two parameters in BETWEEN of querySting?

2004-07-20 04:37

java.util.Date is bad for you, as it includes hour/minute/second-milliseconds along with the date you want as a parameter.

 

in your java code, format your date to 'yyyy-MM-dd' and pass it as a String to your report.

 

 

 

 

By: Javat - javat

RE: Read two parameters in BETWEEN of querySting?

2004-07-20 06:34

Thx for your reply,

 

but the BRL_CREATION_DATE is in the database stored as a 'date' type and the dates are already formatted with a SimpleDateFormat before they are used by the jasper report. Is there really no solution to compare if a date is between to other dates with the java.util.Date class?

 

 

 

 

By: Chuck Deal - cdeal

RE: Read two parameters in BETWEEN of querySting?

2004-07-20 06:49

The problem is the way you are using the dates, it is calling the toString method of the Date class. This is probably not what you want because the toString method is not likely to output the date in the same format that the database needs it.

 

If you feel that you need to pass a date in as a parameter instead of a String, use the SimpleDateFormat from within the querystring to cause the date to be returned in the proper format.

 

So you can verify for yourself, build the querystring inside the app the same way you are trying to in the querystring (using the variables). You should see the problem immediately.

 

 

 

 

By: Javat - javat

RE: Read two parameters in BETWEEN of querySting?

2004-07-21 23:47

How can i change the format when i'm already in my report?

 

 

 

 

By: jadawinrules - jadawinrules

RE: Read two parameters in BETWEEN of querySting?

2004-07-21 23:57

You can't format a parameter before it is given to your query string.

Is there a problem with formatting in your java code ?

 

 

 

 

By: Javat - javat

RE: Read two parameters in BETWEEN of querySting?

2004-07-20 06:57

I parsed the String to a date before I send the date to the jasper report. I really did, so that can't be the problem.

 

Thx anyway, but i already figured that out. So the cause must be something else, anybody any idea??

 

 

 

 

By: Chuck Deal - cdeal

RE: Read two parameters in BETWEEN of querySting?

2004-07-20 07:16

I don't think you fully understood. It doesn't make any difference HOW the Date object was created. The toString() method transforms the Date object to the System date format.

 

When I execute the following statement:

 

System.out.println(new java.util.Date());

 

I get this result:

 

Tue Jul 20 10:13:58 EDT 2004

 

That is probably NOT the format that the database wants its date parameters. That is why I suggested that you build the querystring using variables in your app so that you could see the problem.

 

 

 

 

By: Mykel Alvis - evilarchitect

RE: Read two parameters in BETWEEN of querySting?

2004-07-22 06:05

I had this issue because my programming staff can't decide how to add a date to the system. Sometimes they add a timestamp and sometimes a date with no timestamp (in SQL Server, effectively that's a timestamp of MM/dd/yyy 00:00:00.000).

 

My parameters are provided as a text string (from a web request), in the format of MM/dd/yyyy. To get all the records, which are effectively stored in the database as a datetime, here's what I did:

 

-------------------------------------------

DateFormat df = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss.SSS");

Date fromDate, toDate;

fromDate = df.parse((String)request.getParameter(PRM_FROM_DATE)+ " 00:00:00.000");

toDate = df.parse((String)request.getParameter(PRM_TO_DATE) + " 23:59:59.999");

 

 

// Pass toDate and fromDate to the report...

-----------------------------------------------

This has worked for me so far. I pass the dates to the query. To make it SLIGHTLY more efficient, you could take the parsed "toDate", get the milliseconds, add 1 (millisecond to the value) and make a new Date from that. Sub one from the from date.

 

Like this:

------------------------------------------------

Date nextDayDate = new Date(toDate.getTime()+1L);

Date prevDayDate = new Date(fromDate.getTime()-1L);

------------------------------------------------

 

This provides a Date object for "the day before and the day after the from/to date". Then, in your query, you can do a

------------------------------------------------

WHERE $P{prevDayDate} < the_record_date and

the_record_date < $P{nextDayDate}

 

------------------------------------------------

 

assuming you passed those parameters into your report, of course. The < is slightly more efficient in most queries than <=, same with > and >=. The difference should be negligible, so YMMV.