Dynamic Where Clause(Date parameter problem)

Dear all, 
 
Am using the following script in beforeOpen of the dataset, Am able to use and achieve for string values but when i need to pass a date from the report parameter its throwing the error(am using BIRT 3.7) 
 
SQL error #1:ORA-01861: literal does not match format string 
 
Please suggest me how to pass a date to a script in the below situation from report parameter with Date as a data type 
 
***********{ReceivedDateFrom , ReceivedDateTo ,EnteredDateFrom, EnteredDateTo }in the below script*********** 
 
 
or tell me if any changes should be done in the script to take a date parameter.. 
 
 
 
var dateformatter = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd"); 
 
Status=reportContext.getParameterValue("Status"); 
 
ReceivedDateFrom=reportContext.getParameterValue("ReceivedDateFrom"); 
ReceivedDateTo=reportContext.getParameterValue("ReceivedDateTo"); 
EnteredDateFrom=reportContext.getParameterValue("EnteredDateFrom"); 
EnteredDateTo=reportContext.getParameterValue("EnteredDateTo"); 
 
queryString=this.queryText; 
 
 
 
if(Status!=null && Status!=""){ 
queryString=queryString+" and table.status LIKE '" + params["Status"].value.toUpperCase()+"%'"; 
} 
 
 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>if((ReceivedDateFrom !=null || ReceivedDateTo !=null) && (ReceivedDateFrom !="" || ReceivedDateTo !="")){
queryString=queryString+" AND TO_DATE(TO_CHAR(nvl(table.date1,'01-JAN-1000'),'DD-Mon-YYYY'),'DD-Mon-YYYY') BETWEEN '"+ dateformatter.format(ReceivedDateFrom)+"' AND '"+ dateformatter.format(ReceivedDateTo)+"'";

}

if((EnteredDateFrom !=null || EnteredDateTo !=null) && (EnteredDateFrom !="" || EnteredDateTo !="")){
queryString=queryString+" AND TO_DATE(TO_CHAR(nvl(table.date2,'01-JAN-1000'),'DD-Mon-YYYY'),'DD-Mon-YYYY') BETWEEN '" + dateformatter.format(EnteredDateFrom)+"' AND '"+ + dateformatter.format(EnteredDateTo)+"'";

}</pre>
 
 
this.queryText=queryString; 
 
 
 
Thanks in advance 
 
Arjun Manju

Find more posts tagged with

Comments

thuston

Do you have any logging to see the SQL query? 
(you can run C:\Program Files (x86)\Actuate11\BRDPro\eclipse\eclipsec.exe and use System.out.println(queryText); to print to the command prompt.) 
 
It looks like your query will be 
<pre class='_prettyXprint _lang-auto _linenums:0'>...
AND DateType BETWEEN '2010-08-01%' AND '2010-08-18%'
AND DateType BETWEEN '2010-08-01' AND '2010-08-18'
...</pre>
The first line is probably invalid. But I've never tried a wildcard in a Date literal. 
 
Also, what is the purpose of this? 
TO_DATE(TO_CHAR(nvl(table.date1,'01-JAN-1000'),'DD-Mon-YYYY'),'DD-Mon-YYYY') 
What is the original dataType for date1. If it is already a date all of the above does nothing, unless you want to return the NULL rows with a sufficiently low ReceivedFromDate.

arjunmanju

<pre class='_prettyXprint _lang-auto _linenums:0'>[quote name='thuston' date='18 August 2011 - 05:23 AM' timestamp='1313673833' post='81655']
Do you have any logging to see the SQL query?
(you can run C:\Program Files (x86)\Actuate11\BRDPro\eclipse\eclipse[color="#FF0000"]c[/color].exe and use System.out.println(queryText); to print to the command prompt.)

It looks like your query will be
[code]...
AND DateType BETWEEN '2010-08-01' AND '2010-08-18'
AND DateType BETWEEN '2010-08-01' AND '2010-08-18'
...[/code]
The first line is probably invalid. But I've never tried a wildcard in a Date literal.

Also, what is the purpose of this?
[color="#9ACD32"]TO_DATE(TO_CHAR(nvl(table.date1,'01-JAN-1000'),'DD-Mon-YYYY'),'DD-Mon-YYYY')[/color]
What is the original dataType for date1. If it is already a date all of the above does nothing, unless you want to return the NULL rows with a sufficiently low ReceivedFromDate.
[/quote]</pre>
 
 
 
Hi thuston/all, 
 
Sorry that wildcard is not there ......It has been posted by mistake...But i need to fetch the data between two dates and the script is not taking the date which i pass from the parameter...its showing the error as explained above..please suggest me how to overcome 
 
 
Thanking you, 
 
Arjun Manju

thuston

Please log the SQL and show us what it looks like.