oracle date range parameter problem

[2006 IR Open Discussion]

By: Joanne Li - liyazhuo

oracle date range parameter problem

2003-07-07 19:28

Both startDate and endDate are passed in parameters to the xml schema to specify a date range in the query string. Their types are java.lang.String and in the format of dd-MMM-yyyy

(tried java.sql.Date but failed as well)

 

"d_calendar_date" is the field in the oracle database which is of type Date (yyyy-MM-dd hh:mm:ss)

 

In my query:

......

WHERE d_calendar_date>'$P!{startDate}' AND

d_calendar_date<='$P!{endDate}'

returns no data while

WHERE d_calendar_date>'06-JUN-2003' AND

d_calendar_date<='06-JUL-2003'

works correctly.

 

Does anybody have any clue what the problem is?

 

Thanks a lot in advance.

 

Joanne

 

 

 

 

 

 

 

 

 

By: Ryan Johnson - delscovich

RE: oracle date range parameter problem

2003-07-07 22:28

Hi,

 

It looks like you've got an extra ! in there - it should be $P{...}, not $P!{...} as far as I know

 

Ryan

 

 

 

 

By: Joanne Li - liyazhuo

RE: oracle date range parameter problem

2003-07-08 07:37

Thank you Ryan!

But after chaning to $P{...}

I got:

java.sql.SQLException: ORA-01006: bind variable does not exist

 

Joanne

 

 

 

 

By: Ryan Johnson - delscovich

RE: oracle date range parameter problem

2003-07-08 13:03

Sorry,

 

It looks like you do need the ! in $P!{...} when you do a query. The only thing I can think of is that maybe the single quotes mess up JR. In the query example, all the parameters in the query string are surrounded by spaces.

 

What happens when you wrap the date in quotes before passing it to the report?

 

Ryan