Subtract days from an Input Parameter inside an SQL query

[davidbhybrid]

I have an Input Parameter that I would like to subtract a number of days from. This Parameter minus the number of days will be used inside an SQL query. For example:

 

select j.customer, j.receiveddate

from job j

where j.receiveddate < $P{end_date} - 5 days

As my input I would put 01/06/2012 - the value which I would expect to pass to my QRY would be 01/05/2012.

I cannot seem to get this to work. Anyone have any idea? Thanks in advance!

 

Post Edited by djbolduc at 04/20/2012 16:23

[cbarlow3]

Are you sure that $P{end_date} is set to have a data type of java.util.Date?  It looks like you have the right syntax to me. You might need to make sure that you've handled the case of no user input correctly, but otherwise, I don't see a problem.  Have you tried changing your query to this:

select $P{end_date} as end_date, $P{end_date} - 5 days as earlier_date from (values(1)) as t

and then output $F{end_date} and $F{earlier_date} just to see how they evaluate?  Maybe the date subtraction is working fine and the problem is just that the the receiveddate values in your database aren't what you think they are.  Just to be certain:  your query is looking for all jobs that were received MORE than five days before the specified end_date, correct?  Not more recently than.  Also, do all jobs even have a receiveddate value, or might some of them be NULL?

Also, in my above example, I used "from (values(1)) as t", which is a little cheesy, but you need to select from something, and there's no reason in this troubleshooting example to use a real table that has more than one row, so I used a temporary table that I know only has a single row!

Carl

P.S.: If you use my troubleshooting technique and drag these two fields into a detail record, don't forget to format them with an appropriate Date pattern.

[brian.berrelez]

I ran into this same problem and I know how frustrating it can be! I resolved my issue by performing the following.

AND m.posting_date >= CAST($P{StartDate} AS TIMESTAMP) -1 MONTHS

Also, check your error code on the Jasperserver if you can. Mine was reading the following.

DB2 SQL-Error: -182 SQLState: 42816

https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/codes/src/tpc/n182.html