Passing Date Range Parameters

cletehayes

<p><span style="font-size:medium;">[font="calibri;"]I am very new to Birt and I’ve run into a problem which I have not seen addressed in any documentation or group discussion threads.  I’m writing a report to pull the amount billed to an account within a date range specified by the user.  A simplified Select statement is:[/font]</span></p><p> </p><p><span style="font-size:medium;">[font="calibri;"]SELECT “account”, “bill_date”, “bill_amount” FROM “some table”[/font]</span></p><p><span style="font-size:medium;">[font="calibri;"]WHERE “account” = ? AND “bill_date” >= ? AND “bill_date” <= ?[/font]</span></p><p> </p><p><span style="font-size:medium;">[font="calibri;"]My problem is with the date range parameters.  I have created report parameters and data set parameters of type Date (short format “MM/DD/YY”).  So the user must type in the account number, start date, and end date.  For example, 98765 (account), 07/08/13 (start date), 07/10/13 (end date).[/font]</span></p><p> </p><p><span style="font-size:medium;">[font="calibri;"]The report runs fine.  But I only get results for dates 07/08 and 07/09.  It does not include my end date of 07/10.  I have tried using BirtDateTime.addDay(), but it throws an exception error in the Select statement.[/font]</span></p><p> </p><p><span style="font-size:medium;">[font="calibri;"]Is there an easy way to force Birt to include the end date that the user enters? Using <= does not work for me and I don’t want the end users to have to add an extra day to the parameter.[/font]</span></p><p><span style="font-size:medium;">[font="calibri;"]Is it better to just use the “account” = ? in the WHERE statement and then use a Data Set Filter like “bill_date” BETWEEN RP_Start_Date AND BirtDateTime.addDay(RP_End_Date, 1) to filter out the results I want? [/font]</span></p><p> </p><p><span style="font-size:medium;">[font="calibri;"]Any insight would be appreciated.  Thanks.[/font]</span></p>

kclark

<p>Can you post your rptdesign for me to take a look at?</p>