Query returns values +1 day from starting value of parameter

[jkeri]

I have a report with two date parameters. Both are defined as java.sql.DATE 

 

This is my Oracle query WHERE statement 

    'and COL.collection_date    between  $P{STARTDATE} and $P{ENDDATE}'

PROBLEM:

When I run with this WHERE , the report reports results 1 day after the STARTDATE value. For example , I enter date values of 10/3/2016 for STARTDATE and 10/10/2016 for ENDDATE.

The report returns value between 10/4/2016 and 10/10/2016

 

BUT when I change the WHERE to  'and COL.collection_date    between  $P{STARTDATE} - 1 and $P{ENDDATE}', the report returns values between 10/3/2016 and 10/10/2016 which is what I wanted.

 

Is there anyway other than having to do a '-1' for the STARTDATE parameter?

[joseng62]

This is strange issues. 
What is your default expression in the $P{STARTDATE}. 

And when you test report, you test in jasper studio only or in jasper server aswell. 
In jasperstudio, you do have parameter set to prompt and what is the default value set before you run the report. 

Also sound like you might be having a time issues. Could be the classic 00:00 to 23:59 issues. 
Does your database date columns keep time as well. 

You might need to format the date to date time in your sql query. with TO_DATE. 
Seems to be something do with that if you ask me.

 

[kateschiffelbein]

Most likely, the conflict is between the timezone of the date being submitted and the timezone of your jasper server. You may have to cast timezones depending on your setup.

[riodavid]

Looks like a timezone issue.

Print you where clause in report to see what happens.

[mlopez_1]

I work with Oracle DB too and use:

$X{[bETWEEN], FIELD_NAME, PARAM_DATE_START, PARAM_DATE_END}

For params:

 - Only date: java.util.Date

- Timestamp: java.sql.Timestamp.

Must be careful with db fileds if they have hours,minutes,.... or not.

And works fine

Mariano