Parameter Date validation and reformating

Ducharme

I am a newbie to BIRT and may not be stating the right question but here goes. I need to do the following:

Q1) Take a Start_Date and End_Date and ensure that the Start_Date is <= End_Date. These dates need to work in an Oracle 10g query.


I have set up the dates as strings and have the queries working. I added the following code to the validate event handler:


if (params["Start_Date"].value > params["End_Date"].value){
false;
}
else{
true;
}

I tried some tests with different data:
- 10/1/2008 (start date) and 10/2/2008 (end date): everything is fine
- 10/3/2008 (start date) and 10/2/2008(end date): error shown
- 1/1/2009 (start date) and 10/2/2008(end date): NO error shown


When running a simple test, (no query execution, just parameter check), I get an error box saying "org.eclipse.birt.report.service.api.ReportServiceException: Required parameter Start_Date is not set"

Not exactly useful. This brings up 2 points.
- how do I print out a useful message?
- The string test data comparison does not seem to work correctly.

This implies the value of the strings is not easily compared in that form even though javascript says to use the ">" operators. So what to do from that?

- I could try to manipulate the string in some form so I can compare them

- I could input the dates in Date format ( I tried this using short date formating with the above validate() event handler). The comparison so see if 01/01/09 is greater than 10/01/08 also indicated that it was not - returned true. Is this a bug?

I am running Eclipse Version: 3.3.1
Build id: M20070921-1145

I then converted the event handler to the following code:
BirtComp.lessOrEqual(params["Start_Date"].value, params["End_Date"].value);

This worked with the test data shown above - so that seems to take care of the comparison when using Date data type. It does not work when using Strings as input.


So what? If I input this in date format, it is not usable in the Oracle query. Therefore, what is the actual javaScript code to put in the validate to create a valid Oracle Date from the JavaScript Date (or a generated string).

I am thinking something silly like:
- create 2 hidden parameters SD and ED
- read Start_Date (date) and End_Date (date)
- do the comparison - if a problem, setup the output message and send false.
- if okay, make a string out of the passed in Date
- set the params["SD"] = string start date and do the same for the ED.
- use the SD and ED in the query.

I therefore, created the following script in validate for Start_Date

if (BirtComp.lessOrEqual(params["Start_Date"].value, params["End_Date"].value)){
var curdate = new Date(params["Start_Date"]);
var mday = curdate.getDate();
var month = curdate.getMonth();
var year = getFullYear();
params["SD"] = month+"/"+mday+"/"+year;
curdate = new Date(params["End_Date"]);
mday = curdate.getDate();
month = curdate.getMonth();
year = getFullYear();
params["ED"] = month+"/"+mday+"/"+year;
true;
}
else {
false;
}


Unfortunately, all I get is the mystery error message and I cannot tell what is wrong.


So, Finally. What is the best way to enter and compare the dates and then, if necessary, convert them to strings so I can use Oracle to_date() to use them in queries. I would appreciate actual code for the JavaScript if possible.

Thank you

mwilliams

Hi Ducharme,

I have not tried this at all, but one thing you could possibly do is use string parameters. Then in the validate script, change the 2 string dates to date objects with the something like the following javascript.

df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
df2 = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
myStartDate = df.parse(params["StartDate"].value);
myEndDate = df2.parse(params["EndDate"].value);

Then do your comparison of these date values with the way that worked for you. This way, your parameters stay in string format for you to pass to you Oracle database. Again, I have not tested this, so I'm not 100% sure it'll work, but it's worth a shot. Let me know.