iReport - Optional between date parameters

[justin.brown11]

Hi all,

I've got a simple SQL query that I'm trying to get optional between date parameters working for. 

At the moment, I've got this working:

SQL-wise it should simply just be:

1. WHERE START_DATE BETWEEN START_DATE AND END_DATE 

Jaspersoft-wise the input controls need to be optional

• WHERE (START_DATE >= $P{REPORT_START_DATE} OR $P{REPORT_START_DATE} IS NULL
• AND START_DATE <=  $P{REPORT_END_DATE} OR $P{REPORT_END_DATE} IS NULL)

Problem is, whilst I'm getting no errors when both the REPORT_START_DATE and REPORT_END_DATE are populated I'm seeing start dates that are greater than the report end date?

Edit: 

Adding AND ($X{BETWEEN, CODE_START_DATE,  $P{1.REPORT_START_DATE}, $P{2.REPORT_END_DATE}}) to the end of the SQL Script gives me an "Unrecognized SQL ecape 'Between'" error. :( 

[Sad H]

$P!{REPORT_START_DATE} now it is handled at report side, do this for all the optional parameters.

[justin.brown11]

@Sad H - Can you elaborate? 

Adding "!" Just to the $P's just gives me errors and went breaking it down to just:
(D552M.CODE_START_DATE >= $P!{1.REPORT_START_DATE} AND D552M.CODE_START_DATE <= $P!{2.REPORT_END_DATE})

Doesn't help either..

[justin.brown11]

FYI

For anyone who stumbles across this. The fix is relatively simply, Just treat each line seperately.

• WHERE (START_DATE >= $P{REPORT_START_DATE} OR $P{REPORT_START_DATE} IS NULL)
• AND (START_DATE <=  $P{REPORT_END_DATE} OR $P{REPORT_END_DATE} IS NULL)